YEAR, MONTH, DAY extraction in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that breaks down the sales dates into year, month, and day to analyze seasonal trends.

📊 Data: You have a sales data table with columns for Order ID, Product, Sale Date, and Sales Amount.

🎯 Deliverable: Create a new table that extracts the Year, Month, and Day from the Sale Date column for each order.

Sample Data

Order ID	Product	Sale Date	Sales Amount
1001	Notebook	2023-01-15	25.00
1002	Pen	2023-02-20	5.00
1003	Backpack	2023-03-05	45.00
1004	Calculator	2023-01-25	30.00
1005	Notebook	2023-02-10	25.00
1006	Pen	2023-03-15	5.00
1007	Backpack	2023-01-30	45.00
1008	Calculator	2023-02-25	30.00

1

Step 1: Insert three new columns next to the Sale Date column and label them Year, Month, and Day.

Expected Result

New columns appear labeled Year, Month, and Day next to Sale Date.

2

Step 2: In the first cell under the Year column (assume cell D2), enter the formula to extract the year from the Sale Date.

=YEAR(C2)

Expected Result

For Sale Date 2023-01-15, the formula returns 2023.

3

Step 3: In the first cell under the Month column (assume cell E2), enter the formula to extract the month number from the Sale Date.

=MONTH(C2)

Expected Result

For Sale Date 2023-01-15, the formula returns 1.

4

Step 4: In the first cell under the Day column (assume cell F2), enter the formula to extract the day number from the Sale Date.

=DAY(C2)

Expected Result

For Sale Date 2023-01-15, the formula returns 15.

5

Step 5: Copy the formulas in D2, E2, and F2 down to all rows with sales data.

Drag the fill handle from D2:F2 down to row 9.

Expected Result

All rows show correct Year, Month, and Day extracted from each Sale Date.

Final Result

Order ID | Product    | Sale Date  | Year | Month | Day
-------------------------------------------------------
1001     | Notebook   | 2023-01-15 | 2023 | 1     | 15
1002     | Pen        | 2023-02-20 | 2023 | 2     | 20
1003     | Backpack   | 2023-03-05 | 2023 | 3     | 5 
1004     | Calculator | 2023-01-25 | 2023 | 1     | 25
1005     | Notebook   | 2023-02-10 | 2023 | 2     | 10
1006     | Pen        | 2023-03-15 | 2023 | 3     | 15
1007     | Backpack   | 2023-01-30 | 2023 | 1     | 30
1008     | Calculator | 2023-02-25 | 2023 | 2     | 25

✓All sales dates are from the year 2023.

✓Sales occurred in months 1 (January), 2 (February), and 3 (March).

✓Day extraction allows detailed daily analysis of sales.

Bonus Challenge

Create a new column that shows the month name (e.g., January, February) instead of the month number.

Show Hint

Use the TEXT function with the format code "mmmm" on the Sale Date, like =TEXT(C2, "mmmm").

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