How to Use the DATE Function in Google Sheets
=DATE(year, month, day). Out-of-range months and days roll over, so DATE(2026,13,1) is January 1, 2027.
What DATE does
Typing a date straight into a formula is risky, because Sheets may read 10/10/2000 as a division. DATE builds a real date from three separate numbers, so the result is always unambiguous and works as input for other date functions.
DATE syntax
=DATE(year, month, day)
Per Google's help page:
- All three inputs must be numbers. Text, or a cell that contains text, returns #VALUE!.
- Years from 0 to 1899 have 1900 added to them (
DATE(119,2,1)is February 1, 2019). Years from 1900 to 9999 are used as entered. Years below 0 or above 10,000 return #NUM!. - Decimals are truncated, so a month of 12.75 is treated as 12.
A step-by-step example
To make Christmas Day 2026:
=DATE(2026, 12, 25)
This returns December 25, 2026. Here is why: the three numbers are read as year, month and day, in that order. Many people expect day first. Always use year, month, day.
The numbers can come from cells. Here the year, month and day are stored in separate cells A2, B2 and C2:
| A | B | C | |
|---|---|---|---|
| 1 | Year | Month | Day |
| 2 | 2026 | 3 | 15 |
=DATE(A2, B2, C2)
This returns March 15, 2026.
Rollover: months and days that overflow
Google's page states that out-of-range values roll over into the next period. DATE(1969,13,1) becomes January 1, 1970, and DATE(1969,1,32) becomes February 1, 1969. This makes DATE useful for arithmetic. DATE(2026, 12, 31 + 1, ...) is awkward, but =DATE(2026, 3, 0) gives the last day of February, because day 0 rolls back one day from March 1.
Common DATE mistakes
- Wrong order. It is year, month, day, not day, month, year.
- Text instead of numbers. A month stored as text returns #VALUE!.
- A two-digit year.
DATE(26,1,1)adds 1900 and gives 1926, not 2026. - The cell shows a number. Format the cell as a date.
Try it yourself
Use the cells above for exercise 2. Type your formula into the box, starting with =, then press Check.
Exercise 1: Christmas Day
Write a formula that returns December 25, 2026 using DATE.
Exercise 2: Date from cells
Write a formula that builds a date from the year in A2, the month in B2 and the day in C2.
What to learn next
- Days between two dates: three ways to count the gap.
- EDATE: move a date forward or back by months.
- TODAY: return the current date.
- EOMONTH: return the last day of any month.
- YEAR, MONTH, DAY: pull parts out of a date.
Syntax and rules in this guide were checked against Google's DATE help page. See all guides.