How to Extract the Year, Month and Day From a Date in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner · 5 min read · includes 2 free exercises

In short: =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:

FunctionReturns
=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
1Date
27/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

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.

Hint: pass the cell reference.

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.

Hint: add 1 to the year part only.

What to learn next

Syntax and rules in this guide were checked against Google's YEAR, MONTH and DAY help page. See all guides.