Excel EDATE Function
last modified October 5, 2026
The EDATE function is a powerful date function in Excel that
returns a date a specified number of months before or after a start date.
This tutorial provides a comprehensive guide to using the EDATE
function with detailed examples. You'll learn basic syntax, practical
applications, and advanced techniques.
EDATE Function Basics
The EDATE function calculates a date a specified number of months
before or after a given start date. It's particularly useful for financial
calculations, project planning, and any scenario requiring date manipulation.
| Component | Description |
|---|---|
| Function Name | EDATE |
| Syntax | =EDATE(start_date, months) |
| Arguments | start_date (date), months (integer) |
| Return Value | Serial number representing the date; apply a date format to display it |
This table breaks down the essential components of the EDATE
function. It shows the function name, syntax, argument requirements, and the
serial-number return value.
| Argument | Meaning |
|---|---|
| start_date | The date to count from. It can be a date serial number, a date entered
with the DATE function, or a reference to a cell that holds a
valid date. Text that Excel cannot read as a date returns
#VALUE!. |
| months | The number of whole months to move. A positive value yields a future
date, a negative value yields a past date, and 0 returns the
start date unchanged. A decimal value is truncated toward zero, so
1.9 behaves like 1 and -1.9 behaves
like -1. |
Both arguments are required. EDATE returns a serial number, not a
formatted date, so the result cell must carry a date number format before it
looks like a calendar date.
Basic EDATE Example
This example demonstrates the simplest use of the EDATE function with a fixed date and month offset.
=EDATE("2023-01-15", 3)
This formula calculates the date 3 months after January 15, 2023. The result will be April 15, 2023, which is serial number 45031. The date must be in a format Excel recognizes, and the result cell should be date-formatted.
EDATE with Cell References
A more practical use of EDATE involves referencing cells for both
the start date and month offset. Here's an example with cell references.
| A | B | C |
|---|---|---|
| Start Date | Months | Result |
| 2023-03-10 | 6 | =EDATE(A2, B2) |
The table shows a simple spreadsheet with a start date in A2 and months offset in B2. The EDATE formula in C2 calculates the resulting date.
=EDATE(A2, B2)
This formula calculates the date 6 months after March 10, 2023. The result will be September 10, 2023, serial number 45179. Using cell references makes the formula dynamic: change either input and the result updates.
EDATE with Negative Months
EDATE can calculate dates in the past by using negative numbers for the months argument. This example shows this functionality.
| A | B | C |
|---|---|---|
| Start Date | Months | Result |
| 2023-05-20 | -4 | =EDATE(A2, B2) |
This table demonstrates EDATE's ability to calculate past dates. The negative value in B2 (-4) tells EDATE to calculate 4 months before the start date.
=EDATE(A2, B2)
This formula calculates the date 4 months before May 20, 2023. The result will
be January 20, 2023, serial number 44946. Negative values provide flexibility
for date calculations. A months value of 0 returns the start date
unchanged, which is useful when the offset is supplied dynamically and may be
zero.
EDATE and Month-End Overflow
EDATE keeps the day of the month and only changes the month. The
one exception is overflow: when the target month has fewer days than the start
date's day, the result is clamped to that month's last day. This is not the
same as always landing on a month end. A start date of January 31 becomes
February 28, but a start date of February 28 becomes March 28, not March 31.
| Start Date | Months | Result | Serial |
|---|---|---|---|
| 2023-01-31 | 1 | 2023-02-28 | 44985 |
| 2024-01-31 | 1 | 2024-02-29 | 45351 |
| 2023-03-31 | -1 | 2023-02-28 | 44985 |
| 2023-01-31 | 3 | 2023-04-30 | 45046 |
| 2023-02-28 | 1 | 2023-03-28 | 45013 |
The first three rows show clamping: January 31 clamps to February 28 in a common year and to February 29 in the leap year 2024, and March 31 minus one month clamps back to February 28. The fourth row clamps to April 30, the last day of a 30-day month. The last row is the important counterexample: February 28 plus one month is March 28, because 28 does not overflow March. The day-of-month is preserved unless it is impossible.
=EDATE("2023-01-31", 1) ' 2023-02-28
=EDATE("2024-01-31", 1) ' 2024-02-29, leap year
=EDATE("2023-03-31", -1) ' 2023-02-28
=EDATE("2023-02-28", 1) ' 2023-03-28, no clamp needed
Because of this rule, do not assume that EDATE produces a
month-end date. If the goal is always the last day of a month, use
EOMONTH instead.
EDATE for Loan Payment Schedule
EDATE is commonly used to create payment schedules. This example shows how to generate a series of monthly payment dates.
| A | B |
|---|---|
| Start Date | 2023-06-15 |
| Payment 1 | =EDATE(B1, 1) |
| Payment 2 | =EDATE(B2, 1) |
| Payment 3 | =EDATE(B3, 1) |
This table demonstrates creating a payment schedule where each payment date is exactly 1 month after the previous one. The formulas reference the cell above.
=EDATE(B1, 1) // First payment =EDATE(B2, 1) // Second payment =EDATE(B3, 1) // Third payment
These formulas create a sequence of monthly payment dates starting from June 15, 2023. Each formula calculates 1 month after the previous date, maintaining the same day of month when possible.
EDATE with DATE Function
EDATE can be combined with the DATE function for more flexible date creation. This example shows this powerful combination.
=EDATE(DATE(2023, 3, 1), 5)
This formula first creates March 1, 2023 using DATE, then adds 5 months. The result will be August 1, 2023. Combining these functions allows dynamic date creation and manipulation.
EDATE vs EOMONTH vs DATE
Three functions shift dates by months, and choosing the right one avoids
off-by-a-few-days bugs. EDATE preserves the day of the month and
clamps only on overflow. EOMONTH always returns the last day of
the target month. A DATE formula such as
DATE(YEAR(A1), MONTH(A1)+1, DAY(A1)) looks similar to
EDATE but lets the day overflow into the following month instead
of clamping.
=EDATE("2023-01-31", 1) ' 2023-02-28, clamps
=EOMONTH("2023-01-31", 1) ' 2023-02-28, last day of Feb
=EDATE("2023-02-28", 1) ' 2023-03-28, keeps day 28
=EOMONTH("2023-02-28", 1) ' 2023-03-31, last day of Mar
=DATE(2023, 2, 31) ' 2023-03-03, overflows past Feb 28
Notice how the DATE approach differs from EDATE:
EDATE turns January 31 into February 28 by clamping, while
DATE(2023, 2, 31) builds an invalid February date that Excel rolls
forward to March 3. Use EDATE for anniversaries, maturity dates,
and due dates that should stay on the same day number; use EOMONTH
for billing periods and month ends; use DATE when you need to
control each component yourself.
EDATE Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| start_date is text Excel cannot read as a date | #VALUE! error | Pass a real date or build it with the DATE function |
| Result falls before January 1, 1900 or after December 31, 9999 | #NUM! error | Reduce the month offset or use a date inside the supported range |
| months contains a decimal such as 1.9 | Fractional part silently dropped | Round the value yourself if you need a different result |
| Cell shows a number such as 44985 | Serial number instead of a date | Apply a date number format with Ctrl+1 |
| start_date includes a time of day | The time is removed from the result | Add the time back with MOD(A1,1) if it matters |
| Expecting a month end from a short month | Result keeps the same day number | Use EOMONTH when you need the last day of the month |
The EDATE function is the dependable way to move a date forward or
backward by whole months in Excel. It preserves the day of the month, clamps to
the last valid day only when the target month is too short, and handles leap
years correctly, which makes it ideal for maturity dates, anniversaries,
subscription renewals, and payment schedules. Remember its three rules: the
result is a serial number that needs a date format, the months argument is
truncated toward zero, and month-end dates are not guaranteed unless you reach
for EOMONTH. Once you keep those in mind, month-based date
arithmetic becomes predictable and reliable in any spreadsheet.
Author
List all Excel Formulas.