WEEKDAY and WORKDAY in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the weekday number for a given date

You enter the date 2024-07-04 in cell A1. Which formula correctly returns the weekday number where Sunday = 1 and Saturday = 7?

A =WEEKDAY(A1,2)

B =WEEKDAY(A1,1)

C =WEEKDAY(A1,3)

D =WEEKDAY(A1,11)

💡 Hint

Remember, the second argument in WEEKDAY controls which day is counted as 1.

The WEEKDAY function with return_type 1 counts Sunday as 1 and Saturday as 7. So =WEEKDAY(A1,1) returns 5 for 2024-07-04 (Thursday).

📊 Formula Result

intermediate

Calculate the date 10 working days after a given date

If cell A1 contains the date 2024-06-20, which formula returns the date 10 working days later, excluding weekends?

A =WORKDAY(A1,10)

B =WORKDAY(A1+10,0)

C =A1+10

D =WORKDAY(A1,10, {"2024-06-25"})

💡 Hint

WORKDAY adds working days excluding weekends by default.

=WORKDAY(A1,10) adds 10 working days to the date in A1, skipping Saturdays and Sundays.

Option A adds holidays but none are specified in the prompt.

❓ Function Choice

advanced

Choose the formula that returns the weekday name for a date

You want to display the weekday name (like Monday, Tuesday) for the date in cell A1. Which formula will do this correctly?

A =TEXT(A1, "dddd")

B =WEEKDAY(A1)

C =WORKDAY(A1,1)

D =DAY(A1)

💡 Hint

TEXT function can format dates as text strings.

=TEXT(A1, "dddd") converts the date in A1 to its full weekday name.

WEEKDAY returns a number, WORKDAY returns a date, and DAY returns the day number of the month.

🎯 Scenario

advanced

Calculate the next working day excluding holidays

You have a date in A1 (2024-12-20) and a list of holidays in range B1:B3 (2024-12-25, 2024-12-26, 2025-01-01). Which formula returns the next working day after A1, skipping weekends and these holidays?

A =WORKDAY.INTL(A1,1,"0000011",B1:B3)

B =WORKDAY(A1,1)

C =WORKDAY(A1,1,B1:B3)

D =A1+1

💡 Hint

WORKDAY can take holidays as a third argument to exclude them.

=WORKDAY(A1,1,B1:B3) adds one working day to A1, skipping weekends and the specified holidays.

Option C uses WORKDAY.INTL with a weekend pattern but the pattern "0000011" means Friday and Saturday are weekends, which is not standard.

❓ data_analysis

expert

Count how many weekdays fall between two dates excluding holidays

You want to count how many working days (Monday to Friday) are between 2024-06-01 (in A1) and 2024-06-30 (in B1), excluding holidays listed in C1:C3. Which formula gives the correct count?

A =COUNTIF(A1:B1,"<>Saturday")-COUNTIF(A1:B1,"<>Sunday")

B =WEEKDAY(B1)-WEEKDAY(A1)-COUNT(C1:C3)

C =WORKDAY(B1-A1,0,C1:C3)

D =NETWORKDAYS(A1,B1,C1:C3)

💡 Hint

NETWORKDAYS counts working days between two dates excluding holidays.

=NETWORKDAYS(A1,B1,C1:C3) counts weekdays between the two dates, skipping weekends and holidays.

Other options either misuse functions or do not count days properly.

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