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").