How to Extract the Year, Month and Day From a Date in Google Sheets
=YEAR(A2), =MONTH(A2) and =DAY(A2) return the year, month number and day of the month of a date. Combine them with DATE to build new dates.
The three functions
Each takes one date and returns one part of it:
| Function | Returns |
|---|---|
=YEAR(date) | The year |
=MONTH(date) | The month of the year, as a number |
=DAY(date) | The day of the month |
Per Google's help pages, the date must be a cell reference containing a date, a function that returns a date (such as DATE, DATEVALUE or TO_DATE), or a date serial number. They do not convert typed text the way a cell does, so YEAR(10/10/2000) is read as a division. The help page for MONTH adds that it is most useful inside other calculations, since the month of a known date is easy to read directly.
A step-by-step example
Cell A2 holds July 20, 1969.
| A | |
|---|---|
| 1 | Date |
| 2 | 7/20/1969 |
Dates in the tables are shown in month/day/year order. The results do not depend on your display format.
=YEAR(A2)
This returns 1969. In the same way, =MONTH(A2) returns 7 and =DAY(A2) returns 20.
Build a new date from the parts
The three functions pair naturally with DATE. To find the same date one year later:
=DATE(YEAR(A2) + 1, MONTH(A2), DAY(A2))
This returns July 20, 1970. Here is why: YEAR, MONTH and DAY take the date apart, the year is raised by 1, and DATE puts the pieces back together.
Common mistakes
- Typing the date inside the formula. Use a cell or DATE.
- Passing text. A date stored as text is not a date.
- Expecting a month name. MONTH returns a number. For a name, see month and day names.
Try it yourself
Use the date above (A2). Type your formula into the box, starting with =, then press Check.
Exercise 1: The year
Write a formula that returns the year of the date in A2.
Exercise 2: One year later
Write a formula that returns the date one year after the date in A2, built from YEAR, MONTH and DAY.
What to learn next
- DATE: build a date from a year, month and day.
- EDATE: move a date forward or back by months.
- Day name from a date: show Monday, Tuesday and so on.
Syntax and rules in this guide were checked against Google's YEAR, MONTH and DAY help page. See all guides.