Align contract renewal dates to month-end, then count down the months left
EOMONTH rounds a date stepped forward by whole months onto the last day of wherever it lands, in the same move — and DATEDIF's "M" unit turns two dates into a whole number of months instead of a span of days nobody asked for.
Task:
You handle renewals for the IT department's software maintenance contracts. To keep invoicing simple, every contract is set to renew on the last day of a month rather than on the exact anniversary of its start date. For each contract below, use EOMONTH in column D to step the start date in column B forward by its term length in column C and land on that month's last day — the renewal date. Then, measuring from today's review date of September 23, 2026, use DATEDIF in column E to work out how many whole months remain until each renewal.
Learning Objectives:
- Use EOMONTH to shift a date by whole months and round it to a month-end in one step
- Recognize that EOMONTH discards the original day of the month rather than preserving it
- Measure a countdown from a fixed reference date with DATEDIF's "M" unit, rather than from each row's own date
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Contract | Start Date | Term (Months) | Renewal Date | Months Until Renewal |
| 2 | Atlas ERP Suite | 1/15/2026 | 12 | ||
| 3 | Beacon CRM | 5/3/2026 | 6 | ||
| 4 | Cobalt Analytics | 2/28/2026 | 18 | ||
| 5 | Delta Support Desk | 7/1/2026 | 3 |
What this exercise teaches (contains the answer)
EOMONTH(B2,12) starts at Atlas ERP Suite's January 15, 2026 start date, steps forward twelve months to January 2027, and rounds down to that month's last day, January 31 — the 15th never survives the trip, because EOMONTH only ever hands back a month-end. Cobalt Analytics shows why that matters even when the start date is already a month-end: February 28, 2026 plus an 18-month step still lands on August 31, 2027, not some date built by adding days onto the 28th. Column E then measures from a fixed review date, September 23, 2026, rather than from each contract's own start date, so DATEDIF("9/23/2026",D2,"M") counts the whole months every row still has left as of that same day — 4 for Atlas, 2 for Beacon, 11 for Cobalt, 1 for Delta — with the leftover days beyond the last whole month dropped rather than rounded up into an extra one.