YEAR, MONTH, DAY extraction in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a report that breaks down each order date into year, month, and day to analyze sales trends by these time units.

📊 Data: You have a list of orders with order IDs and order dates in the format YYYY-MM-DD.

🎯 Deliverable: Create a table that shows the original order date along with separate columns for Year, Month, and Day extracted from the order date.

Sample Data

Order ID	Order Date
1001	2023-01-15
1002	2023-02-20
1003	2023-03-05
1004	2023-04-10
1005	2023-05-25
1006	2023-06-30
1007	2023-07-15
1008	2023-08-22
1009	2023-09-18
1010	2023-10-05

1

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

Expected Result

Columns labeled Year, Month, and Day appear next to Order Date.

2

Step 2: In the first cell under the Year column (e.g., C2), enter the formula to extract the year from the order date.

=YEAR(B2)

Expected Result

The year 2023 appears for the first order date 2023-01-15.

3

Step 3: In the first cell under the Month column (e.g., D2), enter the formula to extract the month from the order date.

=MONTH(B2)

Expected Result

The month 1 appears for the first order date 2023-01-15.

4

Step 4: In the first cell under the Day column (e.g., E2), enter the formula to extract the day from the order date.

=DAY(B2)

Expected Result

The day 15 appears for the first order date 2023-01-15.

5

Step 5: Copy the formulas in the Year, Month, and Day columns down for all rows with order dates.

Drag the fill handle from C2:E2 down to row 11.

Expected Result

All rows show the correct year, month, and day extracted from each order date.

Final Result

Order ID | Order Date | Year | Month | Day
------------------------------------------
1001     | 2023-01-15 | 2023 | 1     | 15
1002     | 2023-02-20 | 2023 | 2     | 20
1003     | 2023-03-05 | 2023 | 3     | 5
1004     | 2023-04-10 | 2023 | 4     | 10
1005     | 2023-05-25 | 2023 | 5     | 25
1006     | 2023-06-30 | 2023 | 6     | 30
1007     | 2023-07-15 | 2023 | 7     | 15
1008     | 2023-08-22 | 2023 | 8     | 22
1009     | 2023-09-18 | 2023 | 9     | 18
1010     | 2023-10-05 | 2023 | 10    | 5

✓All order dates are from the year 2023.

✓Months range from January (1) to October (10).

✓Days vary correctly matching each order date.

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 like =TEXT(B2, "mmmm") to get the full month name.