What EDATE does
EDATE moves a date forward or backward by a number of calendar months. You can use it for renewal dates, monthly reviews, or a deadline measured from a starting date.
It keeps the starting day number whenever that day exists in the target month. When the target month is shorter, EDATE uses its last valid day. January 31 plus one month therefore lands on February 28 or 29, depending on the year.
Practical examples
Calculate next month's renewal
Enter =DATE(2024,1,31) in A2 to create January 31, 2024. Enter this formula in B2 and format B2 as a date.
=EDATE(A2,1)The result is February 29, 2024. February has no 31st, so EDATE returns its final day. With January 31, 2023, in A2 instead, the result is February 28, 2023.
Find a date one month earlier
With January 31, 2024, still in A2, use a negative offset.
=EDATE(A2,-1)The result is December 31, 2023. EDATE handles the change of year as part of the month calculation.
Keep monthly reviews tied to the original date
Keep January 31, 2024, in A2. Enter the month offsets 1 and 2 in C2:C3, then enter this formula in D2 and copy it down to D3.
=EDATE($A$2,C2)D2 returns February 29, 2024, and D3 returns March 31, 2024. The fixed reference $A$2 keeps both calculations tied to January 31.
If you calculate March by adding one month to February 29, the result is March 29, 2024. Each call uses the date supplied to it, so a shortened February date can shift later dates in a chained schedule.
Common mistakes and notes
Apply a date format to the result
EDATE returns a date serial number. If you see a number in the result cell, apply a date number format to display the calendar date.
Check the starting date when you get #VALUE!
An invalid start_date produces #VALUE!. Imported text can look like a date without being a usable date value. Use a valid date cell or the DATE function to supply the starting date.
Use whole months for the interval
EDATE truncates fractional month counts toward zero. An offset of 1.9 acts as 1, and -1.9 acts as -1. It does not calculate a partial calendar month.
Choose the rule for month-end deadlines
If every deadline must fall on the last day of a month, use EOMONTH. EDATE preserves the supplied day number where possible, so February 29 plus one month gives March 29.