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.