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.

Practice

1. What does the Excel function YEAR(A1) do if cell A1 contains the date 2024-06-15?

easy

A. Extracts the month part, returning 6

B. Extracts the year part, returning 2024

C. Extracts the day part, returning 15

D. Returns the full date as text

Solution

1. Step 1: Understand the YEAR function

   The YEAR function extracts the year number from a date value.

2. Step 2: Apply YEAR to the date in A1

   Given the date 2024-06-15, YEAR returns 2024.

3. Final Answer:

   Extracts the year part, returning 2024 -> Option B

4. Quick Check:

   YEAR("2024-06-15") = 2024 [OK]

Hint: YEAR extracts the 4-digit year from a date [OK]

Common Mistakes:

• Confusing YEAR with MONTH or DAY functions
• Expecting a text output instead of a number
• Using YEAR on a non-date value

2. Which of the following is the correct formula to extract the month from a date in cell B2?

easy

A. =DATE(B2)

B. =DAY(B2)

C. =YEAR(B2)

D. =MONTH(B2)

Solution

1. Step 1: Identify the function for month extraction

   The MONTH function extracts the month number from a date.

2. Step 2: Check each option

   =MONTH(B2) uses MONTH(B2), which is correct. Others extract day, year, or are invalid.

3. Final Answer:

   =MONTH(B2) -> Option D

4. Quick Check:

   MONTH(date) extracts month number [OK]

Hint: Use MONTH() to get month number from a date [OK]

Common Mistakes:

• Using DAY or YEAR instead of MONTH
• Using DATE function incorrectly
• Missing equal sign in formula

3. If cell C3 contains the date 2023-12-31, what will the formula =DAY(C3) return?

medium

A. 12

B. Error

C. 31

D. 2023

Solution

1. Step 1: Understand the DAY function

   DAY extracts the day number from a date value.

2. Step 2: Apply DAY to the date 2023-12-31

   The day part of 2023-12-31 is 31.

3. Final Answer:

   31 -> Option C

4. Quick Check:

   DAY("2023-12-31") = 31 [OK]

Hint: DAY() returns the day number from a date [OK]

Common Mistakes:

• Confusing day with month or year
• Expecting a date or text output
• Using DAY on a text string not recognized as date

4. A user wrote the formula =YEAR("2024-07-20") but it returns 1900 instead of 2024. What is the likely problem?

medium

A. The date is entered as text, not a date value

B. YEAR function cannot handle dates in July

C. Formula syntax is incorrect, missing parentheses

D. The cell format is set to text

Solution

1. Step 1: Understand YEAR function input requirements

   YEAR expects a valid date value, not a text string.

2. Step 2: Analyze the formula input

   "2024-07-20" is a text string, so YEAR treats it as zero date, returning 1900.

3. Final Answer:

   The date is entered as text, not a date value -> Option A

4. Quick Check:

   YEAR(text) returns 1900 if text not converted to date [OK]

Hint: Ensure date inputs are real dates, not text strings [OK]

Common Mistakes:

• Using quotes around date in YEAR function
• Assuming YEAR works on text dates
• Ignoring cell format effects

5. You have a date in cell D5 and want to create a formula that returns the date in the format YYYY-MM-DD as text using YEAR, MONTH, and DAY functions. Which formula is correct?

hard

A. =YEAR(D5)&"-"&MONTH(D5)&"-"&DAY(D5)

B. =TEXT(D5, "YYYY-MM-DD")

C. =YEAR(D5)+"-"+MONTH(D5)+"-"+DAY(D5)

D. =CONCAT(YEAR(D5), MONTH(D5), DAY(D5))

Solution

1. Step 1: Understand concatenation with YEAR, MONTH, DAY

   Using & to join numbers and text creates a text string.

2. Step 2: Check each option for correct syntax

   =YEAR(D5)&"-"&MONTH(D5)&"-"&DAY(D5) uses & correctly to join year, month, day with dashes. =TEXT(D5, "YYYY-MM-DD") uses TEXT function (valid but not using YEAR, MONTH, DAY). =YEAR(D5)+"-"+MONTH(D5)+"-"+DAY(D5) uses + which causes error. =CONCAT(YEAR(D5), MONTH(D5), DAY(D5)) misses the dashes between parts.

3. Final Answer:

   =YEAR(D5)&"-"&MONTH(D5)&"-"&DAY(D5) -> Option A

4. Quick Check:

   Use & to join parts into text date [OK]

Hint: Use & to join YEAR, MONTH, DAY with dashes for text date [OK]

Common Mistakes:

• Using + instead of & for text concatenation
• Using TEXT function instead of YEAR, MONTH, DAY
• Not adding dashes between parts