SheetsdrillDATE, YEAR, MONTH and DAY
DATE(year, month, day) builds a real date from three numbers, and YEAR, MONTH and DAY return the parts of an existing date as numbers. Dates in Google Sheets are serial numbers (days since December 30, 1899), which is why they can be subtracted and compared.
Syntax
=DATE(year, month, day), =YEAR(date), =MONTH(date), =DAY(date)| Part | What it is |
|---|---|
year | A four-digit year such as 2026. |
month | 1 to 12; values outside that range roll over, so DATE(2026,13,1) is January 1, 2027. |
day | 1 to 31; also rolls over, so DATE(2026,3,0) is the last day of February. |
date | For YEAR, MONTH, DAY: a cell holding a date, a DATE() result or TODAY(). |
Examples
=DATE(2026, 11, 3)Result: 11/3/2026
=MONTH(DATE(2026,11,3))Result: 11
YEAR gives 2026 and DAY gives 3.
=DATE(YEAR(A2), MONTH(A2), 1)Result: The first day of the month of the date in A2
Common mistakes
- MONTH returns a number, not a name, so sorting or labeling with it looks like 1, 2, 3; use TEXT(A2, "mmm") for names.
- A date pasted as text (left aligned) gives #VALUE! in YEAR and MONTH; convert it with DATEVALUE or rebuild it with DATE and SPLIT.
- DATE(26, 11, 3) is not 2026: two-digit years are read as 1926 or similar in Sheets; always use four digits.
Practice DATE, YEAR, MONTH and DAY
These drills check your answer on live data, free, no account needed.
- Orders between two datesCount the orders dated from E1 up to and including E2. Point at the two date cells. Answer in E3.
- Month label with TEXTTurn each invoice date into a label such as "March 2025": full month name, a space, four-digit year. Fill B2 and B3 using TEXT.
Questions
How do I extract the month and year from a date?
=YEAR(A2) and =MONTH(A2) as numbers, or =TEXT(A2, "mmm yyyy") as one label.
How do I convert a text date to a real date?
=DATEVALUE(A2) when the text matches the sheet locale, or =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) for a dd/mm/yyyy string.
Related
- EOMONTH and EDATEHow EOMONTH and EDATE in Google Sheets return the last day of a month or the same day N months later, with examples for due dates and first of month.
- TEXTHow the TEXT function in Google Sheets turns a number or date into formatted text: month labels, currency, percentages and leading zeros, with common codes.
- TODAY and NOWHow TODAY and NOW in Google Sheets return the current date and time, how to calculate days until a deadline, age and overdue flags, and why the value changes.
- COUNTIFSHow COUNTIFS in Google Sheets counts rows where several conditions are true, with examples for status and priority, date ranges and not-equal, plus errors.