WEEKDAY and WORKDAY in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a schedule that shows the day of the week for each employee's start date and calculates their expected last working day after a fixed number of working days, excluding weekends.

📊 Data: You have a list of employees with their start dates and the number of working days they will work.

🎯 Deliverable: Create a table that shows each employee's start date, the weekday name of that start date, and their expected last working day calculated by adding the working days excluding weekends.

Sample Data

Employee	Start Date	Working Days
Alice	2024-06-03	10
Bob	2024-06-04	15
Charlie	2024-06-05	7
Diana	2024-06-06	20
Edward	2024-06-07	5

1

Step 1: Format the 'Start Date' column as Date to ensure correct date handling.

Select the 'Start Date' cells, then Format > Number > Date.

Expected Result

Dates appear in a readable date format like 6/3/2024.

2

Step 2: Create a new column 'Weekday Name' to show the day of the week for each start date.

=CHOOSE(WEEKDAY(B2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Expected Result

For Alice's start date 2024-06-03, the result is 'Monday'.

3

Step 3: Create another new column 'Last Working Day' to calculate the last working day after the given working days, excluding weekends.

=WORKDAY(B2, C2 - 1)

Expected Result

For Alice starting 2024-06-03 with 10 working days, the last working day is 2024-06-14.

4

Step 4: Format the 'Last Working Day' column as Date.

Select the 'Last Working Day' cells, then Format > Number > Date.

Expected Result

Dates appear in a readable date format like 6/14/2024.

Final Result

Employee | Start Date | Weekday Name | Working Days | Last Working Day
---------------------------------------------------------------------
Alice    | 2024-06-03 | Monday       | 10           | 2024-06-14
Bob      | 2024-06-04 | Tuesday      | 15           | 2024-06-24
Charlie  | 2024-06-05 | Wednesday    | 7            | 2024-06-13
Diana    | 2024-06-06 | Thursday     | 20           | 2024-07-03
Edward   | 2024-06-07 | Friday       | 5            | 2024-06-13

✓The WEEKDAY function helps identify the day name of the start date.

✓The WORKDAY function calculates the last working day excluding weekends.

✓For example, Alice starts on Monday, June 3, 2024, and after 10 working days, her last working day is Friday, June 14, 2024.

Bonus Challenge

Modify the 'Last Working Day' calculation to exclude company holidays listed in a separate range.

Show Hint

Use the WORKDAY function's optional third argument to include a range of holiday dates to skip.