5 Ways to Use Formula to Find Next Month in Excel

In this lesson, I’ll demonstrate 5 effective approaches to how to use Excel formulas for calculating next month including some other problems related to this one. These techniques will enable you to solve the problem that led you here. You will master several crucial Excel features and functions during this Excel Formula for Next Month Article, which will come in extremely handy for any assignment using Excel.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Use Formula to Find Next Month in Excel with These 5 Methods

Say we have a dataset of different dates and we want to modify them by changing to a certain date value. Here we will go through different approaches using Excel formulas for determining the next month.

Case 1

 1. Using Excel Formula for Same Day of Next Month

The EDATE Function is quite straightforward. Based on the code, it gives us results in a date format either before or after a particular date. Let’s use a given day to locate the same date in the following month to explain the function.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the Cell you want the answer in.
  • Type in the following formula, the number after the cell number B6 represents what to add with the month if we choose a Negative number the results will show us the Previous month Here we will see the formula for next month so we input 1.
=EDATE(B6, 1)

  • Press Enter to show the result.

  • For the Previous month, you can use the following formula.
=EDATE(B6, -1)

excel formula for next month EDATE function

  • The output for this will look like this

📕 Read More: 8 Ways to Calculate Age Using Formula on a Specific Date in Excel

Case 2

2. Finding Out First Day of Next Month Using Combined Formula in Excel

In this section, we will be using a combination of DATE, YEAR, and MONTH Functions for a custom date. Let’s say we want the First date of the Next month of the same year. Let’s go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, Select a cell for the result and type in the formula given below as per your sheet, or just copy it.


excel formula for next month using DATE YEAR MONTH function

  • Now hit Enter to see results.

  • Now for all the dates copy the formula along the column.

Case 3

3. Calculating End Date of Next Month

The last day of the following month may be quickly obtained by using the EOMONTH function. It gives back the numeric value of the final day of the month either before or after a given number of months.

⬇️⬇️ STEPS ⬇️⬇️

  • To start with, Select a cell and use the following formula. The number after the cell represents the number of months after or before the following date of the cell. The positive number adds months while the negative number reduces.


excel formula for next month Using EOMONTH function

  • Now hit Enter.

  • Copy along the column for other results.

📕 Read More: 4 Suitable Ways to Add Sequential Dates Across Sheets in Excel

Case 4

 4. Displaying Name of Next Month Using Formula

This section is for obtaining the names of the upcoming months based on the actual dates. For the outcomes, the TEXT and EOMONTH functions have to be combined. Follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Select a cell and type in the following formula.


excel formula for next month using TEXT EOMONTH function

  • Press Enter to see the result.

  • Now showing for the rest of the data.

Case 5

5. Calculating Total Days in Next Month

We’ll determine the number of days in the upcoming month in this section. We will be using DAY, DATE, and MONTH Functions here.

⬇️⬇️ STEPS ⬇️⬇️

  • To start, Select a cell and copy the following formula.

=DAY(DATE(YEAR(B6),MONTH(B6) +2, 1) -1)

excel formula for next month using DAY DATE YEAR MONTH Function

  • Hit Enter for the result.

  • Now copy the formula down the column for all the results.

📕 Read More: How to Calculate Overdue Days in Excel

Calculate Dates for Next month with VBA in Excel

Here in this section, we are going to use a VBA code to find the date of the next month on a sheet in Excel. You must enable the developer tab to do this here is a link for your convenience. Follow the instructions below.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Visual Basic option from the Developer tab of the ribbon.

  • Now select Module from the Insert option of the new window.

VBA Code writting Box excel formula for next month

  • Type the following code in the box.
Sub NextMonth()

Dim InputDate As Date 

For i = 6 To 10
InputDate = Cells(i, 2).Value
Cells(i, 3).Value = DateAdd("m", 1, FirstDate)

End Sub

excel formula for next month VBA code

  • Save the code. Now to apply the code go to Macros from the Developer tab of the ribbon.

Deverloper option for excel formula for next month

  • Select the code and press the Run button to continue.

  • Now you’ll have your results as shown.

 excel formula for next month Vba results

📄 Important Notes

🖊️  Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

🖊️  Use shortcuts to reduce time

🖊️  Change the formats as you want them before applying a formula.

🖊️  While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

📝 Takeaways from This Article

📌  Use of DAY, DATE, MONTH, YEAR, EDATE, EOMONTH, and TEXT functions.

📌  How to determine next month’s starting date.

📌  Determining the last date of the next month.

📌  Calculating days of the next month.


I hope you were able to use the techniques I demonstrated in this Excel lesson to find Excel formulas for next month. As you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the Excel Formula for Next Month Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. Again if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. Keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.

Related Articles

(Visited 45 times, 1 visits today)


Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply