Microsoft Excel has provided a wide range of date & time-related formulas that we can use to find dates, days or some other time-related data for next month. In this article, I’ll try to illustrate how we can easily use these Excel formulas to find dates or days for next month.
Download Excel Workbook
You can download the Excel workbook that we’ve used to prepare this article. You can modify data & find the new outputs at once.
6 Quick & Simple Ways to Find Date or Days for Next Month in Excel
1. Getting the Same Date of Next Month with EDATE Function
If we want to get the same date of the following month then the EDATE function is the best-suited option. Let’s get introduced to this function once:
- Formula Syntax:
Returns the serial number of the date that is the indicated number of months before or after the start date.
Before applying this function to find the same date in the next month, we have to format the output cells first.
📌 Step 1:
➤ Select the output cells- C5:C9.
➤ Under the Home ribbon, choose the Short Date format from the drop-down in the Number group of commands.
The selected range of cells is now prepared to show data in date format.
📌 Step 2:
➤ Now select Cell C5 & type:
➤ Press Enter & you’ll get the first output.
➤ Now use Fill Handle to autofill other cells in Column C.
You’ll get the same dates for the following months based on all actual dates from Column B.
2. Obtaining the First Date of Next Month with DATE, YEAR & MONTH Functions
Now we want to find the 1st date of the following month. We have to use DATE, YEAR & MONTH functions together here. YEAR & MONTH functions extract the year & month numbers respectively from a date while the DATE function shows the date in MM/DD/YYYY format.
➤ In Cell C5, we have to type:
➤ Press Enter & then use Fill Handle to autofill all other cells as well in Column C.
3. Finding the Last Date of Next Month with EOMONTH & TODAY Functions
By using the EOMONTH function we can easily get the last date of the following month. Let’s have a glance at the introduction to this EOMONTH function.
- Formula Syntax:
Returns the serial number of the last day of the month before or after a specified number of months.
➤ Select Cell C5 & type:
➤ Press Enter & autofill the entire Column C to get all the outputs.
If you want to get the last date of the next month from the current date then we have to use the TODAY function as the start_date argument in the EOMONTH function.
4. Adding Months to Similar Date from the Original Date
You can find the next same date after a number of months with the EDATE function. Based on our dates in Column B, assuming we want to get the same dates after the next 3 months.
➤ Type in Cell C5:
➤ Press Enter & autofill the rest of the cells in Column C.
5. Showing the Name of the Next Month with TEXT & EOMONTH Functions
If you want to get the names of next months based on original dates then you have to combine TEXT & EOMONTH functions.
➤ We have to type in Cell C5:
➤ Press Enter, autofill the other cells & you’re done.
6. Getting the Number of Days of Next Month with DAY, DATE & MONTH Functions Together
In the last section, we’ll find out the number of days of next month with the combination of DAY, DATE and MONTH functions.
➤ In Cell C5, the formula will be:
=DAY(DATE(YEAR(B5), MONTH(B5) +2, 1) -1)
➤ Press Enter & autofill the rest of the cells in Column C with the Fill Handle option.
I hope all these simple & easy methods mentioned above to find various data for next month will prompt you to apply in your regular Excel chores. If you got any questions or suggestions, then let me know in the comment section. You can check out our other interesting & related Excel articles on this website too.