The EDATE function adds or subtracts a set number of months from any date. It always lands on the same day of the month. For example, adding 3 months to 15 January gives 15 April. You do not need to worry about month lengths, year rollovers, or leap years. Excel handles all of that automatically. This makes EDATE the right tool for contract renewals, payment schedules, warranty dates, and subscription cycles.
What Is the EDATE Syntax?
The EDATE function takes just two arguments. The syntax is short and easy to remember.
| Argument | Required? | What it does |
|---|---|---|
| start_date | Required | The date you want to move forward or backward from. Use a cell reference, the DATE() function, or TODAY(). Do not type dates as plain text — Excel may not read them correctly. |
| months | Required | How many months to move. Use a positive number to move forward in time. Use a negative number to move backward. Decimals are ignored — 2.9 behaves the same as 2. |
=TEXT(EDATE(A2,3),"DD-MMM-YYYY").
How Does EDATE Handle Month-End Dates?
EDATE handles tricky month-end dates well. When you add one month to 31 January, the result is 28 February (or 29 in a leap year). There is no such date as 31 February, so Excel snaps to the last valid day. Similarly, adding one month to 30 November gives 30 December, not 31 December. This behaviour is consistent and predictable.
By contrast, simply adding 30 or 31 days to a date can produce wrong results. For instance, 31 January plus 31 days lands on 3 March, not 28 February. EDATE is the correct approach for any calculation that must respect calendar months.
Example 1: Contract Renewal and Expiry Dates
Contract management is one of the most common uses for EDATE. You can calculate an end date by adding the contract term in months to the start date. The formula updates automatically when you change the start date or the term length.
Example 2: Subscription and Payment Schedules
Subscriptions, loan payments, and billing cycles all repeat at monthly intervals. EDATE generates each payment date from the first payment date. This approach is safer than adding 30 days repeatedly, because months vary in length.
$E$1 for the first payment date. Without the dollar signs, copying the formula down shifts the reference and breaks the schedule.
Example 3: How to Subtract Months from a Date
Negative values in the months argument move the date backward. This is useful for calculating probation periods, look-back windows, and historical comparisons. You simply put a minus sign before the number.
Example 4: Add Years Using EDATE
EDATE works only with months. However, you can add years by multiplying the number of years by 12. This is much more reliable than adding 365 days, because it correctly handles leap years and keeps the day of the month consistent.
Example 5: EDATE with TODAY — Dynamic Rolling Dates
Combine EDATE with TODAY() to create dates that update every day without any manual input. These dynamic formulas are ideal for rolling dashboards, overdue checks, and deadline alerts. Every time the workbook opens, the dates recalculate automatically.
Example 6: EDATE vs EOMONTH — When to Use Each
EDATE has a close relative called EOMONTH. Both functions move a date by a number of months. However, there is one key difference. EDATE preserves the original day of the month. EOMONTH always lands on the last day of the target month. Use EDATE for mid-month dates. Use EOMONTH for billing periods, month-end reports, and financial calculations.
How to Fix Common EDATE Errors
Result shows a number instead of a date
EDATE returns a date serial number. Select the cell and press Ctrl+1. Then choose a Date format from the Number tab. Alternatively, wrap the formula in TEXT: =TEXT(EDATE(A2,3),"DD-MMM-YYYY").
#VALUE! error
Either start_date is not a valid date, or months is not a number. Check the start_date cell. Dates stored as text look like dates but fail in formulas. Use =ISNUMBER(A2) to test whether Excel recognises it as a real date. If it returns FALSE, re-enter the date properly or use DATEVALUE() to convert it.
Wrong result when adding to January 31
This is not an error — it is correct behaviour. EDATE adjusts to the last valid day of the target month. Adding one month to 31 January gives 28 or 29 February because February never has 31 days. If you need the result to stay on day 31, use a different approach based on your business rule.
Frequently Asked Questions About EDATE
-
What does the EDATE function do in Excel?EDATE adds or subtracts a set number of months from a date. It returns a new date on the same day of the month. For example, EDATE(15-Jan-2026, 3) returns 15-Apr-2026. Use a positive number to move forward in time. Use a negative number to move backward. The function handles month lengths, year rollovers, and leap years automatically.
-
How do I subtract months from a date with EDATE?Use a negative number for the months argument. For example, =EDATE(A2, -3) subtracts three months from the date in A2. To subtract a variable number of months stored in a cell, enter the negative value in that cell and reference it: =EDATE(A2, B2) where B2 contains -3.
-
What is the difference between EDATE and EOMONTH?EDATE preserves the original day of the month. EOMONTH always returns the last day of the target month. For example, both functions with a start date of 15 January and 2 months added return different results — EDATE gives 15 March and EOMONTH gives 31 March. Use EDATE for mid-month dates. Use EOMONTH for month-end reporting and billing cut-offs.
-
Can EDATE add years instead of months?Yes. Multiply the number of years by 12 in the months argument. For example, =EDATE(A2, 2*12) adds exactly 2 years. This is more reliable than adding 730 days, because it correctly handles leap years and preserves the day of the month. With years stored in a cell B2, use =EDATE(A2, B2*12).
-
Why does EDATE return a number instead of a date?Excel stores dates as serial numbers. EDATE returns that number, and it only appears as a date when the cell is formatted as a date. Press Ctrl+1, go to the Number tab, and choose Date. Alternatively, wrap the formula in TEXT() with a format code: =TEXT(EDATE(A2,3),"DD-MMM-YYYY") to display the result as a formatted string.
-
Which Excel versions support EDATE?EDATE works in all Excel versions from Excel 2007 onwards. This includes Excel 2010, 2013, 2016, 2019, 2021, 2024, and Microsoft 365 on both Windows and Mac. It is also available in Excel for the web. You do not need any special Excel version or add-in to use it.