YEAR, MONTH, DAY extraction in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to extract the year from a date in cell A1.

Excel

=YEAR([1])

Drag options to blanks, or click blank then click option'

AB1

BA1

CDATE

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function name instead of a cell reference.

Referencing the wrong cell.

The YEAR function extracts the year part from the date in cell A1.

2fill in blank

medium

Complete the formula to extract the month from a date in cell B2.

Excel

=MONTH([1])

Drag options to blanks, or click blank then click option'

AB2

BTODAY()

CC2

DA2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference.

Confusing MONTH with DAY or YEAR functions.

The MONTH function extracts the month part from the date in cell B2.

3fill in blank

hard

Fix the error in the formula to extract the day from a date in cell C3.

Excel

=DAY([1])

Drag options to blanks, or click blank then click option'

ADATE

BC4

CNOW()

DC3

Attempts:

3 left

💡 Hint

Common Mistakes

Referencing a cell without a date.

Using a function name instead of a cell reference.

The DAY function extracts the day part from the date in cell C3. Using the correct cell reference is important.

4fill in blank

hard

Fill both blanks to create a formula that extracts the year and month from cell D4 and combines them as Year-Month.

Excel

=YEAR([1])&"-"&MONTH([2])

Drag options to blanks, or click blank then click option'

AD4

BE4

CD5

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Using different cells for YEAR and MONTH causing inconsistent results.

Referencing a cell without a date.

Both YEAR and MONTH functions should reference the same cell D4 to extract year and month from the same date.

5fill in blank

hard

Fill all three blanks to create a formula that extracts year, month, and day from cell E5 and formats as YYYY/MM/DD.

Excel

=YEAR([1])&"/"&MONTH([2])&"/"&DAY([3])

Drag options to blanks, or click blank then click option'

AE5

BF5

CG5

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Using different cells for each function causing wrong date parts.

Using a function name instead of a cell reference.

All three functions YEAR, MONTH, and DAY should reference the same cell E5 to extract the date parts correctly.

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