Excel track · Exercise 71 of 86 Paid
DATE: build a date from parts
Build a real date from three numbers and let DATE roll the overflow.
Intermediate
Invoice dates split into year, month and day columns
Read-only data · screen 1 of 2 · answer in E2
Column E wants the raised date as a real date number built from the year, month and day in B2:D2, so start with INV-2001 in E2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Year | Month | Day | Date |
| 2 | INV-2001 | 2024 | 3 | 1 | |
| 3 | INV-2002 | 2024 | 4 | 15 | |
| 4 | INV-2003 | 2024 | 5 | 31 |
In B3, build the first day of the month after the statement month in B2: March means April.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Statement month | 3 |
| 3 | First day of next month |
Reference answer
What it does
A date in Excel is a serial number: the count of days since the end of 1899, with 1 January 1900 as day 1. The grid stores that serial, and a formatted date is only what the cell draws. DATE builds the serial from three ordinary numbers, a year, a month and a day, and hands back a real date that sorts and subtracts.
The first screen wants 1 March 2024 in E2, which is serial 45352 built from 2024, 3 and 1. The second asks for the first day of April, so the month argument holds B2+1. DATE rolls the parts over instead of returning an error: month 13 is January of the next year, and day 0 is the day before the first.
That behaviour saves a table of month lengths. The first of next month is the current month plus one, and the last day of this month is day zero of the next. Expressions work in any part, which is how B2+1 becomes April.
Syntax
=DATE(year, month, day)Worked example
On Invoice dates split into year, month and day columns, =DATE(B2,C2,D2) returns 45352.
=DATE(B2,C2,D2)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | A part is text that does not read as a number, so DATE has nothing to build the serial from. |
#NUM! | A negative year, or parts that push the result past 31 December 9999, fall outside the serials Excel holds. |
Wrong month | A month built as B2+2 rolls into the following month, so 1 May appears where 1 April was asked for. |
At work
A finance export with the year, month and day in three columns, turned into one date column the ledger can sort and age.