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.

Practice

1. What does the Excel function =WEEKDAY(A1) return when A1 contains a date?

easy

A. The date of the next workday after the date in A1

B. A number representing the day of the week for the date in A1

C. The total number of workdays in the month of A1

D. The name of the day (like Monday) for the date in A1

Solution

1. Step 1: Understand WEEKDAY function purpose

   The WEEKDAY function returns a number from 1 to 7 representing the day of the week for a given date.

2. Step 2: Apply to cell A1

   Since A1 contains a date, =WEEKDAY(A1) returns the day number of that date.

3. Final Answer:

   A number representing the day of the week for the date in A1 -> Option B

4. Quick Check:

   WEEKDAY = day number [OK]

Hint: WEEKDAY gives day number, not name or date [OK]

Common Mistakes:

• Thinking WEEKDAY returns the day name
• Confusing WEEKDAY with WORKDAY
• Expecting a date instead of a number

2. Which of these is the correct syntax to find the next workday after the date in cell B2, excluding weekends?

easy

A. =WORKDAY(B2, 1)

B. =WORKDAY(B2 + 1)

C. =WEEKDAY(B2, 1)

D. =WORKDAY(B2, "next")

Solution

1. Step 1: Recall WORKDAY syntax

   WORKDAY(start_date, days) returns the date after adding 'days' workdays to start_date, skipping weekends.

2. Step 2: Apply to find next workday

   To find the next workday after B2, add 1 workday: =WORKDAY(B2, 1) is correct syntax.

3. Final Answer:

   =WORKDAY(B2, 1) -> Option A

4. Quick Check:

   WORKDAY(date, 1) = next workday [OK]

Hint: WORKDAY needs two arguments: date and days [OK]

Common Mistakes:

• Using WORKDAY with only one argument
• Confusing WEEKDAY with WORKDAY
• Passing text like "next" instead of a number

3. If cell C3 contains the date 2024-06-14 (a Friday), what will =WORKDAY(C3, 3) return?

medium

A. 2024-06-17 (Monday)

B. 2024-06-18 (Tuesday)

C. 2024-06-20 (Thursday)

D. 2024-06-19 (Wednesday)

Solution

1. Step 1: Identify start date and days to add

   Start date is 2024-06-14 (Friday). We add 3 workdays, skipping weekends.

2. Step 2: Count workdays skipping weekend

   Adding 1 workday: Monday 2024-06-17
   Adding 2 workdays: Tuesday 2024-06-18
   Adding 3 workdays: Wednesday 2024-06-19

3. Final Answer:

   2024-06-19 (Wednesday) -> Option D

4. Quick Check:

   WORKDAY adds days skipping weekends [OK]

Hint: Count only weekdays, skip Sat & Sun [OK]

Common Mistakes:

• Counting weekend days as workdays
• Adding days directly without WORKDAY
• Confusing date formats

4. The formula =WORKDAY(A1, 5, {"2024-06-19"}) returns an error. What is the likely cause?

medium

A. WORKDAY does not accept a third argument

B. The days argument cannot be a positive number

C. The holidays argument must be a range, not an array of text strings

D. The date in A1 is invalid

Solution

1. Step 1: Understand WORKDAY holiday argument

   The third argument for WORKDAY is a range or array of dates, not text strings in quotes.

2. Step 2: Identify error cause

   Using {"2024-06-19"} passes a text string, not a date value, causing an error.

3. Final Answer:

   The holidays argument must be a range, not an array of text strings -> Option C

4. Quick Check:

   Holidays must be date values, not text [OK]

Hint: Holidays must be date cells or date arrays, not text [OK]

Common Mistakes:

• Passing holiday dates as text strings
• Ignoring the third argument format
• Assuming WORKDAY only takes two arguments

5. You want to find the weekday number of the date that is 10 workdays after 2024-06-01, excluding weekends and the holiday 2024-06-12. Which formula correctly does this?

hard

A. =WEEKDAY(WORKDAY("2024-06-01", 10, DATE(2024,6,12)))

B. =WORKDAY(WEEKDAY("2024-06-01"), 10, DATE(2024,6,12))

C. =WEEKDAY(WORKDAY("2024-06-01", 10, {"2024-06-12"}))

D. =WORKDAY("2024-06-01", 10, "2024-06-12")

Solution

1. Step 1: Calculate date 10 workdays after 2024-06-01 excluding holiday

   Use WORKDAY with start date, days, and holiday as DATE(2024,6,12) to get correct date.

2. Step 2: Find weekday number of that date

   Wrap WORKDAY result inside WEEKDAY to get the day number.

3. Final Answer:

   =WEEKDAY(WORKDAY("2024-06-01", 10, DATE(2024,6,12))) -> Option A

4. Quick Check:

   WORKDAY then WEEKDAY for weekday number [OK]

Hint: Use WORKDAY first, then WEEKDAY to get day number [OK]

Common Mistakes:

• Passing holiday as text instead of DATE function
• Using WEEKDAY inside WORKDAY incorrectly
• Ignoring holidays in WORKDAY