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.