DATE
Turn three numbers into a date the spreadsheet can do arithmetic with.
Syntax
DATE(year, month, day)| Argument | Required | Description |
|---|---|---|
year | Required | The four-digit year. |
month | Required | The month. Values outside 1–12 roll into neighbouring years. |
day | Required | The day. Values outside the month's length roll too, and 0 means the last day of the previous month. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=DATE(2024,3,15) | 45366 | A date is a number — see below. |
=DATE(2024,13,1) | 45658 | Month 13 becomes January of the next year. |
=DATE(2024,3,0) | 45351 | Day 0 is the last day of February — a common trick for month ends. |
=DATE(2024,3,15)-DATE(2024,3,1) | 14 | Subtracting dates gives days between them. |
A date is a number
DATE(2024,3,15) returns 45366, not 15/03/2024. Every date in a
spreadsheet is a count of days since 30 December 1899, and the date you see in a
cell is that number wearing a display format.
This is why date arithmetic works at all: + 1 is tomorrow, and subtracting two
dates gives the days between them without any special function.
It is also why a date can look wrong when it is right. A cell showing 45366
holds a perfectly good date that has not been formatted as one.
Rolling is a feature
Out-of-range values do not error — they roll:
=DATE(2024, 13, 1) → 1 January 2025
=DATE(2024, 3, 0) → 29 February 2024DATE(year, month + 1, 0) is the idiomatic way to get the last day of a month
without special-casing February or leap years, and it is worth knowing even
though EOMONTH says the same thing more clearly.
Excel compatibility
Matches Excel, including the rolling behaviour for out-of-range months and days.