Subscription Renewal Dates with EDATE and EOMONTH
Your task: In D2:E5, find each renewal date and the last day of the renewal month.
Full instructions, scenario and goal
Scenario
A software company sells subscriptions of different lengths. Each customer has a start date and a plan length in months. Finance wants the renewal date of each subscription, and the last day of the renewal month, which is when the invoice is cut.
Your task
- In D2:D5, calculate the renewal date: the start date plus the number of months in the same row.
- In E2:E5, find the last day of the month in which each subscription renews.
- Use the start date and the months from the cells, then select Check Answer.
You are done when
- Renewal dates move by calendar months and handle short months correctly.
- The cut-off is always a month-end date.
What you will practise
Move a date forward by a number of calendar months with EDATE, and find the last day of a month with EOMONTH.
The idea in 30 seconds
Dates in Excel are numbers, so adding 30 to a date moves it 30 days. Months, however, have different lengths: "one month after 31 January" is not 31 January plus 30 days. EDATE(start, months) moves a date by a whole number of calendar months and keeps the day of the month where it can, or uses the last day of a shorter month: one month after 31 January 2024 is 29 February 2024. A negative number of months moves backwards.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell D2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Dates in Excel are numbers, so adding 30 to a date moves it 30 days. Months, however, have different lengths: "one month after 31 January" is not 31 January plus 30 days. EDATE(start, months) moves a date by a whole number of calendar months and keeps the day of the month where it can, or uses the last day of a shorter month: one month after 31 January 2024 is 29 February 2024. A negative number of months moves backwards.
EOMONTH(start, months) works the same way but always returns the last day of the resulting month, which is handy for billing cut-offs and month-end reports. Both functions return a serial number, so format the cell as a date to read it. Because they take the date and the number of months from cells, the whole schedule updates when a start date or a plan length changes.
Skills you will practise
- EDATE
- EOMONTH
- Dates
- Month arithmetic
Complete first: Due Dates and Days Overdue
Common mistakes to avoid
- Adding 30 days per month
- Months are 28 to 31 days long, so multiplying months by 30 drifts away from the true date.
- Forgetting to format as a date
- EDATE returns a serial number. The cells are already formatted as dates here, but in your own sheets you must apply a date format.