YEAR, MONTH, DAY extraction in Excel - Cell-by-Cell Formula Trace

Sample Data

This table shows dates in column A. Columns B, C, and D will extract the year, month, and day from each date.

Cell	Value
A1	Date
A2	2024-06-15
A3	2023-12-01
A4	2022-01-31
B1	Year
C1	Month
D1	Day

Formula Trace

=YEAR(A2)

Step 1: A2

Step 2: YEAR(2024-06-15)

Cell Reference Map

    A       B      C      D
1 | Date  | Year | Month | Day  |
2 | 2024-06-15 -> YEAR() used here
3 | 2023-12-01
4 | 2022-01-31

The formula references cell A2 to extract the year.

Result

    A           B      C      D
1 | Date      | Year | Month | Day  |
2 | 2024-06-15 | 2024 |       |      |
3 | 2023-12-01 |      |       |      |
4 | 2022-01-31 |      |       |      |

The YEAR formula in B2 extracts 2024 from the date in A2.

Sheet Trace Quiz - 3 Questions

What does the formula =YEAR(A2) return if A2 contains 2024-06-15?

A 6

B 15

C 2024

D 06-15-2024

Key Result

YEAR(date) extracts the year; MONTH(date) extracts the month; DAY(date) extracts the day from a date.

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