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

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel has provided a wide range of date and time-related functions that we can use to find dates, days, or some other time-related data for next month. In this article, we’ll try to illustrate how we can easily use these Excel formula to find dates or days for the next month.


How to Use Excel Formula to Find Date or Days for Next Month: 6 Ways

For ease of understanding, we are going to use a list of Reference Dates. This dataset includes some Actual Dates in column B.

excel formula for next month

Now, we’ll use multiple Excel formulas to find dates or days from these dates for the next month.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Getting 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. It returns the serial number of the date, which is the indicated number of months before or after the start date. Allow us to demonstrate the process below.

📌 Steps:

Before applying this function to find the same date in the next month, we have to format the output cells first.

  • At the very beginning, select the output cells in the C5:C9 range.
  • Then, under the Home tab ribbon, choose the Short Date format from the drop-down in the Number group of commands.

Getting Same Date of Next Month with EDATE Function

The selected range of cells is now prepared to show data in Date format.

  • Now, select cell C5 & enter the following formula.
=EDATE(B5,1)

Here, B5 represents the first cell of the Actual Date column.

  • After that, press ENTER and you’ll get the first output.

Using EDATE Function to Get Same Date of Next Month

  • At this time, bring the cursor to the right-bottom corner of cell C5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
  • Following this, double-click on it.

Using Fill Handle Tool

You’ll get the same dates for the following months based on all actual dates from column B.

Excel Formula to Get Same Date for Next Month

Read More: Get the First Day of the Current Month in Excel


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 the DATE, YEAR & MONTH functions together here. YEAR and MONTH functions extract the year and month numbers respectively from a date while the DATE function shows the date in MM/DD/YYYY format. So, follow these simple steps to do the task successfully.

📌 Steps:

  • First of all, select cell C5 and insert the formula below.
=DATE(YEAR(B5),MONTH(B5)+1,1)
  • After that, press the ENTER key.

Excel Formula to Obtain the First Date of Next Month with DATE, YEAR & MONTH Functions


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. It returns the serial number of the last day of the month before or after a specified number of months. Let’s see the process in detail.

📌 Steps:

  • First, go to cell C5 and paste the following formula.
=EOMONTH(B5,1)
  • Consequently, hit ENTER.

Excel Formula to Find the Last Date of Next Month with EOMONTH & TODAY Functions

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.

Read More: How to Get Last Day of Previous Month in Excel


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:

  • Firstly, select cell C5 and write down the formula below.
=EDATE(B5,3)
  • Secondly, tap the ENTER key.

Adding Months to Similar Date from the Original Date

Read More: Excel Formula for Current Month and Year


5. Showing the Name of the Next Month with TEXT & EOMONTH Functions

If you want to get the names of the next months based on original dates, then you have to combine TEXT and EOMONTH functions. To do this using this method, you may follow the below steps.

📌 Steps:

  • Initially, we have to select cell C5 and enter the following formula.
=TEXT(EOMONTH(B5,1),"mmmm")
  • Then, press ENTER, and you are done.

Showing the Name of the Next Month with TEXT & EOMONTH Functions


6. Getting the Number of Days of Next Month with DAY, DATE & MONTH Functions Together

In the last method, we’ll find out the number of days of the next month with the combination of DAY, DATE, and MONTH functions. Let’s see it in action.

📌 Steps:

  • Primarily, get into cell C5 and put down the formula below.
=DAY(DATE(YEAR(B5), MONTH(B5) +2, 1) -1)
  • Secondarily, press the ENTER key.

Getting the Number of Days of Next Month with DAY, DATE & MONTH Functions Together


How to Create Automatic Rolling Months in Excel

Sometimes, we need to put the same date values with the only difference in consecutive months in our Excel workbook. It’s simple & easy, just follow along to create automatic rolling months in Excel.

📌 Steps:

  • At first, select cell B5 and write down a particular date. Here, we wrote 22-05-2021 in that cell.

How to Create Automatic Rolling Months in Excel

  • Secondly, drag the Fill Handle down to cell B14.

As a result, the cells will be filled with rolling dates. But you need rolling months.

  • For doing this, click on the Auto Fill Options icon and choose the option Fill Months.

As a result, you will be able to create automatic rolling months in Excel using the Fill Handle. And, the final result should look like this.

Excel Formula to Create Automatic Rolling Month in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Practice Section


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

That’s all from me today. This article explains how to use Excel formula to find date or days in the next month in a simple and concise manner. Don’t forget to download the practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


Further Readings


<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo