Dates and reporting
EDATE function: add calendar months and handle month-end clipping
Schedule monthly equipment checks from a start date with EDATE, and see why anchoring offsets to the original start date preserves the 31st after February.
Starting a monthly schedule on January 31
Suppose a compressor installed on 31 January 2026 (serial 46053 in B2) requires service after 1 month (C2 = 1) and 2 months (C3 = 2). Adding a fixed 30 days misses calendar month boundaries; EDATE shifts by whole calendar months instead.
| Anchor start (B2) | Months ahead | Resulting calendar date | 1900 serial |
|---|---|---|---|
| 31 January 2026 (46053) | 1 | 28 February 2026 | 46081 |
| 31 January 2026 (46053) | 2 | 31 March 2026 | 46112 |
Clip shorter months without losing the original day
One month after 31 January 2026 cannot be 31 February, so =EDATE(B2, 1) clips to the last valid day of February: 28 February 2026 (46081). Two months from the same anchor =EDATE($B$2, 2) lands on 31 March 2026 (46112).
=EDATE($B$2,C2)Why chaining +1 month from the previous row changes March
If your March formula adds 1 month to February’s clipped result—EDATE(EDATE(B2, 1), 1)—it starts from 28 February and returns 28 March 2026 (46109) instead of 31 March (46112). Anchor recurring schedules to the original start date with month numbers 1, 2, 3 when contracts belong on the original day of the month. Negative offsets step backward: EDATE(B2, -1) returns 31 December 2025 (46022).
Try the example
Use the original values or change them to test your understanding.