How to Use EDATE in Excel
Adds or subtracts whole months from a date while keeping the same day of the month — perfect for due dates and renewals.
What is EDATE in Microsoft Excel?
EDATE shifts a date forward or back by a number of months, preserving the day where the calendar allows it. A subscription starting on the 15th renews on the 15th. If the target month is shorter, Excel clamps to the last valid day, so 31 January plus one month becomes 28 or 29 February.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| start_date | Required | The date to shift. |
| months | Required | Months to add. Negative values move backwards. |
When to Use EDATE (Real-World Business Scenarios)
- 1
Calculating a subscription renewal date three, six or twelve months out.
- 2
Setting warranty expiry from a purchase date.
- 3
Building a rolling 12-month forecast with =EDATE(start,ROW()-1).
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Start Date | Plan | Renewal |
|---|---|---|
| 2026-01-15 | Monthly | 2026-02-15 |
| 2026-01-31 | Monthly | 2026-02-28 |
| 2026-01-15 | Yearly | 2027-01-15 |
How it works: Moves the start date forward exactly one calendar month. The 31 January case clamps to 28 February because that month has no 31st.
Common Errors & How to Fix Them
Why it happens: The output cell is formatted as General instead of Date.
Fix: Apply a date format such as dd-mmm-yyyy.
Why it happens: This is expected clamping. A date on the 31st cannot survive February.
Fix: If the day must be preserved, store the intended day number separately and rebuild with DATE.
Try EDATE Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (EDATE)
What does the EDATE formula do in Excel?
EDATE shifts a date forward or back by a number of months, preserving the day where the calendar allows it. A subscription starting on the 15th renews on the 15th. If the target month is shorter, Excel clamps to the last valid day, so 31 January plus one month becomes 28 or 29 February.
What is the syntax for EDATE?
What are common mistakes when using EDATE?
Result is a serial number: The output cell is formatted as General instead of Date. (Apply a date format such as dd-mmm-yyyy.) Day drifts after a short month: This is expected clamping. A date on the 31st cannot survive February. (If the day must be preserved, store the intended day number separately and rebuild with DATE.)
Related Excel Formulas
Keep building your formula toolkit with these date & time functions.
- TODAYReturns the current date, refreshed every time the workbook recalculates — the anchor for ageing reports.
- DATEDIFReturns the difference between two dates in complete years, months or days — the standard way to calculate age and tenure.
- EOMONTHReturns the last day of a month, any number of months before or after a given date — the backbone of monthly reporting.
- NETWORKDAYSCounts working days between two dates while excluding weekends and your own holiday list.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
- VLOOKUPThe industry-standard vertical lookup function that searches the first column of a table and returns a value in the same row.
Master EDATE in Intermediate Excel
Learn EDATE along with our formula library, practice files, and verifiable certificates in our complete curriculum.