DATE()
Builds a date from separate year, month, and day numbers.
DATE(year, month, day)DATE assembles a proper date serial number from three numbers, which is useful when year/month/day come from separate cells or formulas rather than being typed as a date directly.
It tolerates out-of-range values by rolling over: DATE(2026, 13, 1) becomes January 1, 2027, which makes it handy for date math like "the first of next month."
DATE is the safe way to construct a date because it never depends on regional settings — DATE(2026,3,4) means the fourth of March everywhere, while the text "03/04/2026" means different things in different places. That makes it the right building block inside criteria, where a date written as text will silently match nothing. Its rollover behaviour is deliberate and useful: month 13 becomes January of the next year and day 0 becomes the last day of the previous month, so month arithmetic works without any checking of month lengths.
Arguments
- year
- The year, e.g. 2026.
- month
- The month as a number, 1–12 (or outside that range, which rolls over into adjacent years).
- day
- The day of the month.
Examples
=DATE(2026, 7, 24)Builds a date from three separate numbers.
=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)A common pattern for rolling date math.
=DATE(2026, 13, 1)Out-of-range arguments roll over rather than erroring. Month 13 becomes January of the next year, which makes DATE safe for month arithmetic.
Notes and gotchas
- DATE builds a date from three numbers, which makes it immune to the locale ambiguity that plagues typed dates —
DATE(2026,3,4)is unambiguous where03/04/2026is not. - It handles out-of-range parts by rolling over:
DATE(2026,13,1)is January 2027, andDATE(2026,3,0)is the last day of February. The zero-day trick is a compact month-end formula. - A two-digit year argument is interpreted as 1900-something or 2000-something depending on the value, which is a real hazard when the year comes from parsed text. Always pass four digits.
- This is the right way to build a date criterion for
SUMIFS:">="&DATE(2026,1,1), since a date written inside quotes is read as text and matches nothing. - The result is a serial number; format the cell as a date or you will see a five-digit integer.
Common errors
| You see | Because | Fix |
|---|---|---|
| #NUM! | The resulting date is before 1 January 1900, Excel's epoch. | Excel cannot represent earlier dates as serial numbers. Store historical dates as text if you need them. |
| #VALUE! | A non-numeric argument. | Wrap parsed text parts in VALUE first. |
DATE tricks, tips, and workbook hacks
DATE constructs a valid, locale-independent Excel serial date from three separate numbers: year, month, and day. Unlike typing "03/04/2026" (which means March 4 in the US and April 3 in the UK), =DATE(2026, 3, 4) is unambiguous everywhere in the world.
DATE also features built-in calendar rollover arithmetic: pass month = 13 and it rolls into January of the next year; pass day = 0 and it steps back to the very last day of the prior month!
Quick DATE checks before trusting the result
- Always build hardcoded date constants inside formulas with DATE(2026, 1, 1) rather than quoted strings like "1/1/2026".
- Use =DATE(YEAR(A2), MONTH(A2), 1) to snap any daily transaction date back to the 1st of its month.
- Use =DATE(YEAR(A2)+1, MONTH(A2), DAY(A2)) to calculate a 1-year contract renewal date.
- Always ensure the year argument is a full 4-digit year (2026, not 26), because DATE adds 1900 to values between 0 and 1899.
| DATE pattern | DATE formula | Best use | Watch out |
|---|---|---|---|
| Reassemble YYYYMMDD integer exports into true Excel dates | =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) | Converting ERP or SQL integer dates like 20260315 into real Excel dates you can filter and pivot. | DATE automatically coerces the text outputs of LEFT/MID/RIGHT into numbers. |
| First day of the current month from any date | =DATE(YEAR(A2),MONTH(A2),1) | Normalizing daily invoice dates into monthly cohort buckets. | Format the cell as mmm yyyy so all rows in March display "Mar 2026" while storing 2026-03-01. |
| Last day of the month using the day = 0 rollover trick | =DATE(YEAR(A2),MONTH(A2)+1,0) | Finding month-end dates (including Feb 28 vs Feb 29 leap years) by asking for day 0 of the following month. | EOMONTH(A2, 0) achieves the same result with a shorter formula. |
| DATE mistake | What happens in DATE | Fix |
|---|---|---|
| Passing a 2-digit year like 26 | =DATE(26, 3, 15) produces March 15, 1926 instead of 2026 because DATE treats years < 1900 as offsets from 1900. | Add 2000 to 2-digit years (=DATE(2000+A2, ...)) or enforce 4-digit year inputs. |
DATE practice drills
- Convert a column of 8-digit numbers (20260115, 20260228, 20261130) into formatted Excel dates using DATE + LEFT/MID/RIGHT.



