YEAR, MONTH, DAY extraction in Excel - Step-by-Step Guide

Introduction

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

When you want to find out the year someone was born from their birthdate.

When you need to group sales data by month to see monthly trends.

When you want to check which day of the month an event happened.

When you want to create a report that shows only the year part of dates.

When you want to filter data by month or day without changing the original date.

Steps

Step 1: Click

- a blank cell where you want the year to appear

The cell is selected and ready for typing

💡 Choose a cell next to your date for easy comparison

Step 2: Type

- the formula bar

The formula appears in the cell

💡 Type =YEAR(A1) if your date is in cell A1

Step 3: Press

- Enter key

The cell shows the year extracted from the date in A1

💡 Make sure the date in A1 is a valid Excel date

Step 4: Repeat

- steps 1 to 3 using =MONTH(A1) to get the month

The cell shows the month number from the date

💡 Month numbers go from 1 (January) to 12 (December)

Step 5: Repeat

- steps 1 to 3 using =DAY(A1) to get the day

The cell shows the day number from the date

💡 Day numbers go from 1 to 31 depending on the month

Before vs After

Before

Cell A1 contains the date 2024-06-15

After

Cell B1 shows 2024 (year), Cell C1 shows 6 (month), Cell D1 shows 15 (day)

Settings Reference

Formula input

📍 Formula bar

Extract year, month, or day from a date

Default: No default, user types formula

Cell format

📍 Home tab > Number group > Number Format dropdown

Ensure the output cell shows numbers, not dates

Default: General

Common Mistakes

Typing the date as text instead of a date value

The YEAR, MONTH, DAY functions only work with valid Excel dates, not text strings

Enter the date using Excel's date format or use DATE function to create a date

Using the wrong cell reference in the formula

The formula will extract from the wrong cell and show incorrect results

Check and use the correct cell reference where the date is located

Summary

YEAR, MONTH, and DAY functions extract parts of a date in Excel.

Use =YEAR(cell), =MONTH(cell), and =DAY(cell) formulas to get year, month, and day.

Make sure your date is a valid Excel date for these functions to work.