by Alexander Frolov, updated on
In this tutorial, you will learn how to use EDATE formula in Excel and combine it with other functions for a variety of purposes beyond its basic use.
Microsoft Excel provides a variety of functions to calculate dates. When it comes to adding or subtracting months to/from a given date, EDATE is the function to use. Additionally, it can help you calculate expiration dates, anniversary dates, due dates, and a lot more.
The EDATE function in Excel returns a date on the same day of the month, n months before or after a given date.
The syntax has two arguments:
And here are a few examples of using the EDATE function in Excel:
Note. By default, the EDATE function returns a serial number representing a date internally in Excel. To display the result as a date, apply a Date format of your choosing.
To make a basic EDATE formula in Excel, follow these steps:
For example, to return a date 5 months after 30 January 2025, use one of these formulas:
=EDATE(DATE(2025, 1, 30), 5)
To get a date 5 months before 30 January 2025, the formulas go as follows:
=EDATE(DATE(2025, 1, 30), -5)
With the start date in a predefined cell, say C4, the formulas are:
The result is a date on the same day of the month, a specified number of months before or after the initial date.
Tips and notes
The EDATE function falls under the category of DATE and TIME functions. Dates in Excel may have different formats, but they are actually stored as numbers. Only valid Excel dates can be used in calculations.
With the Excel EDATE basics in mind, let's drill into a few practical examples that highlight particular aspects and show how to accomplish some common tasks.
To return a date n months before or after today's date, use EDATE in combination with the TODAY function:
For example, to find a date that is 3 months from today, you can use this formula:
To get a date exactly 3 months before today, the formula is:
More formula examples are shown in the screenshot below:
To calculate a date shifted a certain number of years in future or past, you can multiply the number of years by 12:
For instance, to move a date 10 years forward from the date in A3, you can use one of these formulas:
To move a date 10 years backward, the formulas are:
The first form is more contact while the second variation is more convenient to use when you have a number of years in another cell. For example:
The EDATE function correctly returns the last day of month if the start day is 31.
For example, the formula below returns 30-Apr-2023, which is a date 3 months after 31-Jan-2023:
It also maintains the last day of February in leap years. For instance, the result of this formula is 29-Feb-2024:
However, if the start day value is less than 31, EDATE will return a date exactly on the same day of the month in the past or future, as per its main purpose. In this case, you can compute the last day of month by using the EOMONTH function. For example:
The screenshot below shows the results of both formulas - the wrong ones are in the red font color:
Knowing the start date and duration in months, calculating an expiry, due, anniversary or retirement date is as easy as pie.
All you have to do is to supply the start date in the first argument of EDATE and the number of months in the second argument. For example, with the start date in A3 and months in B3, the formula is:
In situation when you need to add or subtract a different number of months to/from a date based on one or more conditions, you can nest an EDATE formula inside of an IF statement.
Let's say you want to calculate an expiry date based on the subscription type - monthly or annual. To have it done, you construct the following nested IF formula:
=IF(B3="monthly", EDATE(A3, 1), IF(B3="annual", EDATE(A3, 12), "N/A"))
If B3 contains the word "monthly", the first EDATE function adds one month to the start date in A3. If B3 has "annual", another EDATE function adds 12 months to the start date. If B3 contains anything else or is blank, the formula returns "N/A".
When the start date value also includes time, the EDATE function drops the time part. To preserve time, extract it from the datetime value with the help of the MOD function, and then add to the EDATE's result:
For instance, to add a certain number of months (B3) to a datetime value in A3 preserving the time, use this formula:
=EDATE(A3, B3) + MOD(A3, 1)
That's how to use the EDATE function in Excel to add or subtract months to/from a given date. Thank you for reading and see you on our blog next week!
Using EDATE formula in Excel - examples (.xlsx file)
Table of contents