WEEKDAY and WORKDAY in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an HR assistant at a company.

📋 Request: Your manager wants a report showing the day of the week for each employee's start date and the date when their 10th working day will be.

📊 Data: You have a list of employees with their start dates. Weekends are Saturday and Sunday. No holidays are considered.

🎯 Deliverable: Create a table that shows each employee's name, start date, the weekday name of the start date, and the date of their 10th working day after the start date.

Sample Data

Employee	Start Date
Alice	2024-06-03
Bob	2024-06-04
Charlie	2024-06-05
Diana	2024-06-06
Edward	2024-06-07

1

Step 1: Add a new column named 'Weekday' next to the 'Start Date' column.

In the first cell under 'Weekday' (e.g., C2), enter the formula: =TEXT(B2, "dddd")

Expected Result

For Alice (2024-06-03), the result is 'Monday'.

2

Step 2: Add another column named '10th Workday Date' next to the 'Weekday' column.

In the first cell under '10th Workday Date' (e.g., D2), enter the formula: =WORKDAY(B2, 9)

Expected Result

For Alice starting on 2024-06-03, the 10th working day is 2024-06-14.

3

Step 3: Copy the formulas in columns 'Weekday' and '10th Workday Date' down for all employees.

Drag the fill handle from C2 and D2 down to the last employee row.

Expected Result

Each employee's weekday and 10th workday date are calculated correctly.

Final Result

Employee | Start Date | Weekday   | 10th Workday Date
-----------------------------------------------------
Alice    | 2024-06-03 | Monday    | 2024-06-14
Bob      | 2024-06-04 | Tuesday   | 2024-06-17
Charlie  | 2024-06-05 | Wednesday | 2024-06-18
Diana    | 2024-06-06 | Thursday  | 2024-06-19
Edward   | 2024-06-07 | Friday    | 2024-06-20

✓The TEXT formula shows the full weekday name for each start date.

✓The WORKDAY formula calculates the date of the 10th working day, skipping weekends.

✓This helps the manager plan employee milestones based on working days.

Bonus Challenge

Modify the WORKDAY formula to exclude company holidays listed in a separate range named 'Holidays'.

Show Hint

Use the formula =WORKDAY(B2, 9, Holidays) where 'Holidays' is a range of holiday dates.