Date Functions
Intermediate

Rebuild a date one year on

Take a date apart, change one piece, put it back with DATE.

Task:

Each contract renews on the same day a year after it started. In C2:C6 give the renewal date.

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of YEAR, MONTH, DAY

0/3

The same formula in the other shapes it takes at work.

  1. YEAR, MONTH, and DAY Functions

    Extract year, month, and day from dates using YEAR, MONTH, and DAY.

    Core
    Beginner
  2. A quarter label from a date

    MONTH divided by 3, rounded up, is the quarter.

    Intermediate
  3. Rebuild a date one year on

    Take a date apart, change one piece, put it back with DATE.

    Intermediate

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.

6 rows × 3 columns5 cells you fill in
ABC
1ContractStartRenewal
2C-1013/14/2024
3C-10211/30/2023
4C-1037/1/2024
5C-1042/29/2024
6C-10512/31/2024
What this exercise teachesMay contain the answer

Adding 365 days drifts in a leap year; rebuilding the date keeps the calendar day. Watch 29 February: there is no 29 February 2025, so DATE rolls it to 1 March — check with the contract whether that or 28 February is the intended renewal.