YEAR, MONTH, DAY extraction in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Extract the year from a date

Given the date in cell A1 as 2023-11-15, which formula correctly extracts the year?

A =DAY(A1)

B =MONTH(A1)

C =YEAR(A1)

D =TEXT(A1, "yyyy")

💡 Hint

Think about which function returns the year part of a date.

The YEAR function extracts the year number from a date. MONTH and DAY extract other parts. TEXT with "yyyy" returns the year as a text string.

📊 Formula Result

intermediate

Extract the month from a date

If cell B2 contains the date 2024-02-28, which formula returns the month number?

A =DAY(B2)

B =MONTH(B2)

C =YEAR(B2)

D =TEXT(B2, "MM-DD")

💡 Hint

Which function gives the month number from a date?

The MONTH function returns the month number (1 to 12). DAY returns the day, YEAR returns the year, and TEXT with "MM-DD" returns a text string, not a number.

📊 Formula Result

advanced

Extract the day from a date stored as text

Cell C3 contains the text string "2023/12/05" (not a date value). Which formula extracts the day number correctly?

A =MID(C3, 9, 2)

B =DAY(C3)

C =RIGHT(C3, 2)

D =DAY(DATEVALUE(C3))

💡 Hint

Text dates need to be converted to date values before using date functions.

DAY expects a date value, not text. DATEVALUE converts text to a date. So DAY(DATEVALUE(C3)) extracts the day correctly. RIGHT and MID extract text but return text, not numbers.

❓ Function Choice

advanced

Choose the formula to extract year, month, and day separately

You want to extract year, month, and day from a date in cell D4. Which set of formulas is correct?

A Year: =YEAR(D4), Month: =MONTH(D4), Day: =DAY(D4)

B Year: =TEXT(D4, "DD"), Month: =TEXT(D4, "MM"), Day: =TEXT(D4, "YYYY")

C Year: =DAY(D4), Month: =YEAR(D4), Day: =MONTH(D4)

D Year: =MONTH(D4), Month: =DAY(D4), Day: =YEAR(D4)

💡 Hint

Remember the functions that extract each part of a date.

YEAR, MONTH, and DAY extract the respective parts. Option A uses wrong format codes and swaps year and day. Options A and C mix up the functions.

🎯 Scenario

expert

Calculate age in years from birthdate

Cell E5 contains a birthdate. You want to calculate the person's age in full years as of today. Which formula gives the correct age?

A =DATEDIF(E5, TODAY(), "Y")

B =YEAR(TODAY()) - YEAR(E5) - (MONTH(TODAY()) < MONTH(E5))

C =YEAR(TODAY()) - YEAR(E5)

D =DATEDIF(TODAY(), E5, "Y")

💡 Hint

Use a function that calculates the difference in years correctly considering months and days.

DATEDIF with "Y" calculates full years between two dates correctly. Option A ignores months and days, so it can overestimate age. Option A tries to adjust but has flawed logic. Option A reverses dates causing an error or wrong result.

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