EDATE shifts a date by a number of months. It keeps the day number when that day exists in the target month, otherwise it uses the target month’s last valid day.
EDATE function syntax
=EDATE(start_date, months)- start_date: a numeric date cell or a date built with DATE.
- months: positive to move forward, negative to move backward.
- Fractional month counts are truncated toward zero. Format a numeric result as a date when necessary.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Start date | Months |
|---|---|
| =DATE(2024,1,15) | 3 |
| =DATE(2024,6,15) | -3 |
| =DATE(2024,1,31) | 1 |
| =DATE(2023,1,31) | 1 |
Add months to a starting date
Enter this formula in A8 and apply a date format. January 15, 2024 plus three months is April 15, 2024. Its underlying serial value is 45397.
=EDATE(A2,B2)Result: 45397.
Move backward with a negative month count
The second example starts on June 15, 2024 and subtracts three months. It returns March 15, 2024. Subtracting months is different from subtracting a fixed number of days.
=EDATE(A3,B3)Result: 45366.
Handle dates at the end of a month
January 31, 2024 moves to February 29 because 2024 is a leap year. January 31, 2023 plus one month instead produces February 28, 2023.
=EDATE(A4,B4)Result: 45351.
Calculate an anniversary with leap-day behavior
Twelve months after February 29, 2024 is February 28, 2025. Adding a multiple of twelve does not always preserve the original day and month.
=EDATE(DATE(2024,2,29),12)Result: 45716.
Calculate a subscription renewal boundary
Six months after January 15, 2024 is July 15, 2024. This is a calendar boundary; whether access ends then or includes that whole day depends on the subscription’s stated rule.
=EDATE(DATE(2024,1,15),6)Result: 45488.
Avoid drift in repeated monthly schedules
Two months directly after January 31, 2023 is March 31. Chaining one-month shifts instead goes through February 28 and then reaches March 28.
Calculate each occurrence from the original anchor when that is the intended rule.
=EDATE(DATE(2023,1,31),2)Result: 45016.
Handle fractional months and blank inputs
The count 2.9 is truncated to 2, so January 15 moves to March 15. For optional input cells, use an IF blank check before EDATE rather than interpreting an empty cell as a date.
=EDATE(DATE(2024,1,15),2.9)Result: 45366.
Other Google Sheets articles you may also like