YEAR, MONTH, DAY extraction in Google Sheets - Step-by-Step Guide

Introduction

This feature helps you get the year, month, or day from a date in your spreadsheet. It solves the problem of breaking down a full date into parts you can use separately.

When you want to sort sales data by year to see yearly trends.

When you need to count how many orders came in each month.

When you want to find the day of the month for due dates.

When creating reports that group data by month or year.

When you want to highlight weekends or specific days in a calendar.

Steps

Step 1: Click

- a cell where you want the year to appear

The cell is selected and ready for input

Step 2: Type

- the selected cell

The formula is entered and ready to calculate

💡 Use =YEAR(date_cell) to get the year from a date

Step 3: Press

- Enter key

The cell shows the year extracted from the date

Step 4: Repeat

- for month extraction cell

Use =MONTH(date_cell) to get the month number

Step 5: Repeat

- for day extraction cell

Use =DAY(date_cell) to get the day number

Step 6: Drag

- the fill handle of the formula cell down or across

The formula copies to other cells, extracting year/month/day for each date

Before vs After

Before

Column A has full dates like 2024-06-15, 2023-12-01, 2022-01-30

After

Column B shows years like 2024, 2023, 2022; Column C shows months like 6, 12, 1; Column D shows days like 15, 1, 30

Settings Reference

Date format

📍 Format menu > Number > Date

Ensures the date is recognized correctly for extraction

Default: Depends on locale

Common Mistakes

Using text that looks like a date but is not a real date

The YEAR, MONTH, DAY functions only work on real date values, not text strings

Make sure the cell is formatted as a date or convert text to date using DATEVALUE() before extracting

Typing the formula without parentheses, like =YEAR

Formulas need parentheses to work, even if empty or with arguments

Always include parentheses and the cell reference, e.g., =YEAR(A2)

Summary

YEAR, MONTH, and DAY functions extract parts of a date for easier analysis.

Use =YEAR(date), =MONTH(date), and =DAY(date) with a valid date cell reference.

Make sure your dates are real date values, not text, for correct results.