Dates and reporting
DATE function: build calendar dates from year, month, and day numbers
Construct unambiguous numeric dates from separate year, month, and day columns—and understand why DATE rolls invalid days like February 29, 2026 forward instead of raising an error.
Assemble dates without DD/MM vs MM/DD text ambiguity
When an export supplies numeric Year (column A), Month (column B), and Day (column C) separately, DATE(year, month, day) builds a real serial date regardless of regional locale settings.
| Year (A) | Month (B) | Day (C) | Calendar meaning |
|---|---|---|---|
| 2026 | 2 | 28 | 28 February 2026 (serial 46081) |
| 2026 | 2 | 29 | Rolls to 1 March 2026 (serial 46082) |
Build the date and inspect its serial value
For row 2 (2026, 2, 28), DATE(A2, B2, C2) returns 46081 in the 1900 date system, which displays as 28 February 2026 when formatted as a date. Applying MONTH and DAY to that result returns 2 and 28.
=DATE(A2,B2,C2)DATE rolls overflow days forward—it does not reject bad input
Because 2026 is not a leap year, February has 28 days. Entering 2026, 2, 29 in row 3 does not produce an error: DATE(2026, 2, 29) adds the extra day onto February 28 and returns 46082 (1 March 2026). Day 0 similarly steps back one day: DATE(2026, 3, 0) returns 46081 (28 February 2026).
That calendar arithmetic is handy for offsets, but it means DATE alone is not an input validator. To verify that A2:C2 forms a real calendar date without rollover, check that the resulting month and day still match B2 and C2.
=AND(MONTH(DATE(A2,B2,C2))=B2,DAY(DATE(A2,B2,C2))=C2)Try the example
Use the original values or change them to test your understanding.