Date Functions
Intermediate

DATE rolls over: the first of next month

Month 13 is January of the next year — DATE does the calendar arithmetic for you.

Task:

Invoices for each billing month fall due on the first day of the following month. In C2:C6 give the due date from the year and month in A and B.

Interactive Spreadsheet

Read about 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 DATE

0/3

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

  1. DATE Function

    Create dates from year, month, and day components.

    Core
    Beginner
  2. DATE rolls over: the first of next month

    Month 13 is January of the next year — DATE does the calendar arithmetic for you.

    Intermediate
  3. DATE from a yyyymmdd code

    Cut the year, month and day out of a text code and rebuild a real date.

    Advanced

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
1YearMonthDue date
220243
3202411
4202412
520251
620256
What this exercise teachesMay contain the answer

Handling December by hand needs an IF and a year change. DATE accepts out-of-range months and days and normalises them, so B2+1 just works, and the same trick gives the day before (day 0) or a month back (month 0).