DATE function construction in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a report showing the exact date of each sale based on separate year, month, and day columns.

📊 Data: You have a table with sales data including separate columns for Year, Month, and Day, but no combined date column.

🎯 Deliverable: Create a new column that combines Year, Month, and Day into a proper date using the DATE function.

Sample Data

Sale ID	Year	Month	Day	Amount
101	2024	1	15	250
102	2024	2	5	300
103	2024	3	20	150
104	2023	12	31	400
105	2023	11	10	100
106	2024	4	1	350
107	2024	5	25	275
108	2023	10	15	225

1

Step 1: Insert a new column next to the Day column and label it 'Sale Date'.

Expected Result

A new empty column named 'Sale Date' appears next to the Day column.

2

Step 2: In the first row of the 'Sale Date' column (assuming row 2), enter the formula to combine Year, Month, and Day into a date.

=DATE(B2,C2,D2)

Expected Result

The formula returns 15-Jan-2024 as a date value.

3

Step 3: Copy the formula down the entire 'Sale Date' column for all sales rows.

Drag the fill handle from the first formula cell down to the last row.

Expected Result

Each row shows the correct date combining Year, Month, and Day, e.g., 5-Feb-2024, 20-Mar-2024, 31-Dec-2023, etc.

4

Step 4: Format the 'Sale Date' column to display dates in a readable format like 'dd-mmm-yyyy'.

Select the 'Sale Date' column, right-click, choose Format Cells > Date > select '15-Jan-2024' style.

Expected Result

Dates appear as 15-Jan-2024, 05-Feb-2024, 20-Mar-2024, etc.

Final Result

Sale ID | Year | Month | Day | Amount | Sale Date
-------------------------------------------------
101     | 2024 | 1     | 15  | 250    | 15-Jan-2024
102     | 2024 | 2     | 5   | 300    | 05-Feb-2024
103     | 2024 | 3     | 20  | 150    | 20-Mar-2024
104     | 2023 | 12    | 31  | 400    | 31-Dec-2023
105     | 2023 | 11    | 10  | 100    | 10-Nov-2023
106     | 2024 | 4     | 1   | 350    | 01-Apr-2024
107     | 2024 | 5     | 25  | 275    | 25-May-2024
108     | 2023 | 10    | 15  | 225    | 15-Oct-2023

✓The DATE function correctly combines separate year, month, and day values into valid dates.

✓This allows sorting and filtering sales by exact date easily.

✓Formatting the date column improves readability for reports.

Bonus Challenge

Create a new column that shows the weekday name (e.g., Monday, Tuesday) for each sale date.

Show Hint

Use the TEXT function with the formula =TEXT(F2, "dddd") assuming the Sale Date is in column F.