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

Challenge - 5 Problems

📊 Formula Result

intermediate

Extract the year from a date

Given the date 2024-06-15 in cell A1, which formula correctly extracts the year?

A =MONTH(A1)

B =YEAR(A1)

C =DAY(A1)

D =TEXT(A1, "DD")

💡 Hint

The YEAR function returns the year part of a date.

The YEAR function extracts the year number from a date. Here, =YEAR(A1) returns 2024.

📊 Formula Result

intermediate

Extract the month from a date

If cell B2 contains the date 2023-12-05, which formula extracts the month number correctly?

A =MONTH(B2)

B =DAY(B2)

C =YEAR(B2)

D =TEXT(B2, "MM")

💡 Hint

The MONTH function returns the month number from a date.

The MONTH function extracts the month number from a date. For 2023-12-05, =MONTH(B2) returns 12.

📊 Formula Result

advanced

Extract the day from a date stored as text

Cell C3 contains the text string "2024-07-20" (not a date). Which formula extracts the day number correctly?

A =MONTH(C3)

B =DAY(C3)

C =TEXT(C3, "DD")

D =DAY(DATEVALUE(C3))

💡 Hint

Convert text to date first, then extract day.

Since C3 is text, DAY(C3) fails. Using DATEVALUE(C3) converts text to date, so =DAY(DATEVALUE(C3)) returns 20.

❓ Function Choice

advanced

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

You have a date in cell D4. Which set of formulas correctly extracts year, month, and day respectively?

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

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

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

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

💡 Hint

Remember the YEAR, MONTH, and DAY functions extract parts of a date.

Option A uses the correct functions: YEAR for year, MONTH for month, and DAY for day.

🎯 Scenario

expert

Calculate age in years from birthdate

Cell E5 contains a birthdate (e.g., 1990-04-25). Which formula calculates the person's age in full years as of today?

A =MONTH(TODAY()) - MONTH(E5)

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

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

D =YEAR(E5) - YEAR(TODAY())

💡 Hint

Use DATEDIF to get full years between two dates.

DATEDIF(start_date, end_date, "Y") returns the number of full years between two dates, correctly calculating age.