Syntax
DATE(year, month, day)
| Parameter | Description |
|---|---|
| year | Parameter of the DATE function. |
| month | Parameter of the DATE function. |
| day | Parameter of the DATE function. |
Examples
Build a date from components
Formula
=DATE(A2, B2, C2)
Returns: 2024-06-15 — combines year 2024, month 6, day 15 into a proper date
First day of current month
Formula
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
Returns: 2024-03-01 — constructs the first day of whatever the current month is
Add months to a date
Formula
=DATE(YEAR(A2), MONTH(A2)+3, DAY(A2))
Returns: a date 3 months after A2 — DATE auto-corrects month overflow (e.g., month 15 becomes March of next year)
Common Errors
#VALUE!
One of the arguments is text instead of a number. Ensure year, month, and day are all numeric.
#NUM!
The resulting date is outside the valid range. Year must be between 1900-9999 in most spreadsheets.
Tips
Month overflow
DATE(2024, 13, 1) returns Jan 1, 2025. DATE(2024, 0, 1) returns Dec 1, 2023. This is useful for date arithmetic.
Last day of month
=DATE(YEAR(A1), MONTH(A1)+1, 0) gives the last day of the month in A1. Day 0 rolls back to the previous month's last day.
Two-digit years
DATE(24, 1, 1) is interpreted as year 1924, not 2024. Always use 4-digit years to avoid ambiguity.
Try DATE in Viztab
Import your data and use DATE with 370+ other formulas. No signup required.
Open Viztab