Excel Formula to Find Date or Days in Next Month (6 Quick Ways)

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:

=EDATE(start_date, months)

  • Function:

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.

excel formula to get same date in next month

📌 Step 2:

➤ Now select Cell C5 & type:

=EDATE(B5,1)

➤ 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.

excel formula to get same date in next month


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.

📌 Steps:

➤ In Cell C5, we have to type:

=DATE(YEAR(B5),MONTH(B5)+1,1)

➤ Press Enter & then use Fill Handle to autofill all other cells as well in Column C.

excel formula to get 1st date of next month


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:

=EOMONTH(start_date, months)

  • Function:

Returns the serial number of the last day of the month before or after a specified number of months.

📌 Steps:

➤ Select Cell C5 & type:

=EOMONTH(B5,1)

➤ Press Enter & autofill the entire Column C to get all the outputs.

excel formula to get last date of next month

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.

excel formula to get last date of next month


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.

📌 Steps:

➤ Type in Cell C5:

=EDATE(B5,3)

➤ Press Enter & autofill the rest of the cells in Column C.

excel formula to add month to find next date

➥ Read More: Excel Add Months to Date (4 Easiest Examples)


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.

📌 Steps:

➤ We have to type in Cell C5:

=TEXT(EOMONTH(B5,1),"mmmm")

➤ Press Enter, autofill the other cells & you’re done.

excel formula to find next month name


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.

📌 Steps:

➤ 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.

excel formula to find number of days in next month


Concluding Words

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.


Further Readings

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo