As not all months in the year are the same length if you need to find the same date in a month a fixed number of months forwards or backwards in Excel you can’t simply add a fixed number of days e.g. 30 days for one month ahead, 60 days for two months ahead etc.
Excel has a function which can calculate the date a number of months ahead or behind a given start date, so two months ahead of October 15th 2024 would return December 15th 2024.
This function can help with deadline planning or maintaining consistency with dates relative to a starting date.
Download File
If you would like to follow along download the attachment below.
What is the EDATE function?
The EDATE function is a function that calculates a date that is a specified number of months before or after a given start date.
=EDATE(start_date, months)
start_date: The date from which you want to calculate. This can be a date entered directly (like "01-Jan-2024"), a cell reference (like A1), or a formula that returns a date.
months: The number of months to add or subtract. Use a positive number to get a future date and a negative number for a past date.
Notes on Formatting
Excel treats all dates as serial numbers that can be formatted to appear in different ways.
For example the serial number 45580 represents October 15th 2024 which can then be formatted to appear as 15/10/2024,15-October-24 or a number of other ways.
The date outputs in the examples below have been formatted to show as date/month/year, but depending on how you have your cells formatted the default output for EDATE may be the dates serial number rather than a date format.
If this is the case then you can simply change the output to show in the day/month/year format by going to the Home tab and selecting the drop down from the Number section.
When you click the drop down in the Number section you can select Short Date from the drop down list.
To learn more about changing date formats and why Excel uses as serial number for dates click here.
The Problem With Adding Numbers to Dates to Find Future Dates
As not all months have the same number of days in order to find the same date in the following month a different amount would need to be added each time depending on how many months ahead you wanted to look.
Manually doing this calculation means you need to work out the number of days in each month and how many days you need to add to find the future date.
Using EDATE to Calculate Future Dates
EDATE will take whatever date is entered in the start_date section of the function and give the corresponding date the number of months ahead that you enter in the months section regardless of how many days ahead it is.
If the date in one month does not exist in the next, such as a leap year or if one month has 31 days and the next has 30, then EDATE will give the last date available in the month.
Using EDATE to Calculate Past Dates
To use EDATE to find the day in the month a fixed number of months prior to the chosen date you can enter a negative amount in the months section. Â
Like with forward months if the date in one month does not exist in the previous one then EDATE will give the last date available in the previous month.
Link Months to a Cell Range to Dynamically Change Number of Months
To make EDATE more dynamic and to be able to update it by changing the value in a worksheet the months section can be linked to a cell rather than hard typed into the function.
To link to a cell for previous months you can either enter a minus sign before the number in the cell, or if you want the number in the cell to be a positive put the minus sign before the cell reference in the function.
Conclusion
Finding the same date a fixed number of months ahead or behind a date in Excel can be difficult if manually adding or subtracting a fixed number of days from a given date.
EDATE can dynamically calculate this by giving it a start date and then specifying how many months ahead or backwards you want to look.
It will take different month lengths into account, including leap years.
To make the function more dynamic the number of months you want to move forward or backward by can be entered in a cell which the function can reference.
Comments