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.

Practice

1. What does the YEAR function do in Google Sheets when applied to a date like 2024-06-15?

easy

A. It extracts the year part as a number, e.g., 2024.

B. It extracts the month part as a number, e.g., 6.

C. It extracts the day part as a number, e.g., 15.

D. It converts the date to text format.

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 2024-06-15

   Applying YEAR to 2024-06-15 returns 2024 as the year part.

3. Final Answer:

   It extracts the year part as a number, e.g., 2024. -> Option A

4. Quick Check:

   YEAR(DATE(2024,6,15)) = 2024 [OK]

Hint: YEAR extracts the year number 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 text string not recognized as a date

2. Which of the following is the correct syntax to extract the month from a date in cell A1 in Google Sheets?

easy

A. =MONTH{A1}

B. =MONTH[A1]

C. =MONTH(A1)

D. =MONTH A1

Solution

1. Step 1: Recall correct function syntax

   Functions in Google Sheets use parentheses () around arguments.

2. Step 2: Check each option

   Only =MONTH(A1) uses correct parentheses syntax: =MONTH(A1).

3. Final Answer:

   =MONTH(A1) -> Option C

4. Quick Check:

   Correct function syntax uses parentheses [OK]

Hint: Use parentheses () for function arguments [OK]

Common Mistakes:

• Using square brackets or curly braces instead of parentheses
• Omitting parentheses entirely
• Adding extra spaces inside function name

3. Given cell A1 contains the date 2023-12-25, what will the formula =DAY(A1) return?

medium

A. 12

B. 25

C. 2023

D. Error

Solution

1. Step 1: Understand the DAY function

   DAY extracts the day number from a date value.

2. Step 2: Apply DAY to 2023-12-25

   The day part of 2023-12-25 is 25, so DAY(A1) returns 25.

3. Final Answer:

   25 -> Option B

4. Quick Check:

   DAY(DATE(2023,12,25)) = 25 [OK]

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

Common Mistakes:

• Confusing day with month or year
• Expecting a text string instead of a number
• Using DAY on a non-date value

4. You wrote the formula =MONTH(A1 to extract the month from a date in cell A1, but it shows an error. What is the problem?

medium

A. MONTH function requires two arguments.

B. MONTH function cannot be used on dates.

C. Cell A1 is empty.

D. Missing closing parenthesis ")".

Solution

1. Step 1: Check formula syntax

   The formula =MONTH(A1 is missing the closing parenthesis ")".

2. Step 2: Understand function requirements

   MONTH requires one argument inside parentheses; missing ")" causes syntax error.

3. Final Answer:

   Missing closing parenthesis ")". -> Option D

4. Quick Check:

   Correct syntax =MONTH(A1) [OK]

Hint: Always close parentheses in formulas [OK]

Common Mistakes:

• Forgetting to close parentheses
• Assuming MONTH needs multiple arguments
• Ignoring error messages

5. You have a date in cell B2 as 2024-01-05. You want to create a formula that returns the date in the format "Day: 5, Month: 1, Year: 2024". Which formula will do this correctly?

hard

A. ="Day: " & DAY(B2) & ", Month: " & MONTH(B2) & ", Year: " & YEAR(B2)

B. =CONCATENATE("Day: ", DAY(B2), ", Month: ", MONTH(B2), ", Year: ", YEAR(B2))

C. =TEXT(B2, "Day: d, Month: m, Year: yyyy")

D. =DAY(B2) & "," & MONTH(B2) & "," & YEAR(B2)

Solution

1. Step 1: Understand the goal

   The formula must return text exactly like "Day: 5, Month: 1, Year: 2024" combining text and date parts.

2. Step 2: Check each option

   ="Day: " & DAY(B2) & ", Month: " & MONTH(B2) & ", Year: " & YEAR(B2) uses & to join text and functions correctly. =CONCATENATE("Day: ", DAY(B2), ", Month: ", MONTH(B2), ", Year: ", YEAR(B2)) also works but CONCATENATE is less common now. =TEXT(B2, "Day: d, Month: m, Year: yyyy") uses TEXT with wrong format string, so it won't produce the exact text. =DAY(B2) & "," & MONTH(B2) & "," & YEAR(B2) misses text labels.

3. Final Answer:

   ="Day: " & DAY(B2) & ", Month: " & MONTH(B2) & ", Year: " & YEAR(B2) -> Option A

4. Quick Check:

   Concatenate text and date parts with & [OK]

Hint: Use & to join text and date parts in formulas [OK]

Common Mistakes:

• Using TEXT with wrong format codes
• Forgetting to add text labels
• Using commas without text labels