DATE - Construct unambiguous calendar dates
Build numeric dates from year, month, and day fields, and understand the rollover behavior that can either help or hide invalid input.
By the end, you can
- Build a date from three components.
- Understand month/day rollover.
- Use four-digit years and explicit date formatting.
Before you start: Understand a numeric value versus text that merely looks like a date.
In this lesson
Make dates independent of ambiguous text parsing
DATE combines a year, month, and day into a numeric Excel date. It is useful when HR imports three separate date fields or a schedule needs a clearly specified deadline. DATE(2026,10,8) is unambiguously 8 October, unlike the text 08/10/2026, whose interpretation can depend on regional settings.
Syntax and arguments
=DATE(year, month, day)All components are required. The result is a serial number; apply a date format to show a calendar date.
| Argument | Purpose | Example |
|---|---|---|
yearRequired | Use a four-digit year to avoid DATE's special 0–1899 interpretation. | 2026 |
monthRequired | Month number, with values outside 1–12 rolling into other years. | 10 |
dayRequired | Day number, with overflow or zero rolling across month boundaries. | 8 |
Dates from imported components
08-Oct-2026| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | Constructed date |
| 2 | 2026 | 10 | 8 | 08-Oct-2026 |
| 3 | 2026 | 13 | 1 | 01-Jan-2027 |
| 4 | 2026 | 2 | 0 | 31-Jan-2026 |
=DATE(A2,B2,C2)Year 2026, month 10, and day 8 produce 08-Oct-2026. In the 1900 system its modern serial is 46303.
- Use the numeric component columns instead of concatenating slash-delimited text.
- Enter DATE(A2,B2,C2) in the output column.
- Format D as dd-mmm-yyyy and inspect the resulting calendar dates.
- Validate month/day inputs separately if rollover should not be accepted by the import policy.
Useful rollovers and a leap-year boundary
=DATE(2026,13,1)Month 13 is January of the next year.
=DATE(2026,2,0)Day 0 of February is the final day of January.
=DATE(2024,3,0)The day before March 2024 is 29 February because 2024 is a leap year.
Years, serial systems, and compatibility
- For a year argument from 0 through 1899, DATE adds 1900. DATE(26,10,8) therefore means 1926, not 2026.
- A negative year or a year at least 10000 is invalid. Non-numeric date components can also produce errors; clean imported fields.
- Month/day rollover is intentional behavior, not an input-validation guarantee. A day beyond a month's length can become a date in the next month.
- DATE is available in older/current Excel. The default 1900 system and the optional 1904 system encode the same calendar date with different serials; examples here assume 1900.
Common mistakes and practical tips
DATE is not implemented by the custom workspace and no matching challenge exists. Use its construction and rollover examples in Microsoft Excel.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.