Nehad Ulfat
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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
6 Ways to Use Excel Formula to Find Date or Days for Next Month
For ease of understanding, we are going to use a list of Reference Dates. This dataset includes some Actual Dates in Column B.
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.
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.
- 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.
You’ll get the same dates for the following months based on all actual dates from Column B.
Read More: How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)
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.
Read More: How to Subtract Dates in Excel to Get Years (7 Simple Methods)
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:
- At first, go to cell C5 and paste the following formula.
=EOMONTH(B5,1)
- Consequently, hit ENTER.
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 Apply Excel Formula to Count Days from Date to Today
Similar Readings
- How to Calculate Number of Months Between Two Dates in Excel
- Excel Formula to Calculate Number of Days Between Today and Another Date
- How to Calculate Number of Days Between Two Dates in Excel
- Calculate Years and Months between Two Dates in Excel (6 Approaches)
- How to Count Months from Date to Today by Using Excel Formula
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.
Read More: How to Add Months to Date in Excel (5 Practical Examples)
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.
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.
Read More: Excel Formula for Number of Days Between Two Dates
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.
- 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.
Also, you can check out the article How to Create Automatic Rolling Months in Excel to know more effective ways of doing the same task 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.
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. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more. Happy Excelling! ☕
Further Readings
- How to Calculate Working Days Excluding Sundays in Excel
- Add 3 Months to a Date in Excel (4 Easy Methods)
- How to Calculate 90 Days from a Specific Date in Excel
- Calculate Number of Days between Two Dates with VBA in Excel
- How to Calculate Years Between Two Dates in Excel (2 Methods)
- Add Dates in Excel Automatically (2 Simple Steps)
