WEEKDAY and WORKDAY in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Determine 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)

B =WEEKDAY(A1, 2)

C =WEEKDAY(A1, 3)

D =WEEKDAY(A1, 1)

💡 Hint

The second argument in WEEKDAY controls which day is counted as 1.

The WEEKDAY function returns a number representing the day of the week for a date. Using 1 as the second argument means Sunday is 1 and Saturday is 7. The default is also 1 if omitted.

📊 Formula Result

intermediate

Calculate the date 10 working days after a start date

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

A =A1+10

B =WORKDAY(A1, 10, {"2024-07-04"})

C =WORKDAY(A1, 10)

D =WORKDAY.INTL(A1, 10, "0000011")

💡 Hint

WORKDAY counts only weekdays by default.

The WORKDAY function adds working days to a date, skipping weekends. Option C correctly adds 10 working days. Option C excludes a holiday but is not needed here. Option C adds calendar days, not working days. Option C uses a custom weekend pattern but is more complex.

❓ Function Choice

advanced

Choose the formula to find the weekday name for a date

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

A =TEXT(A1, "dddd")

B =WEEKDAY(A1, 1)

C =WORKDAY(A1, 1)

D =DAY(A1)

💡 Hint

Think about how to convert a date to a text weekday name.

The TEXT function with format "dddd" converts a date to its full weekday name. WEEKDAY returns a number, WORKDAY returns a date offset by working days, and DAY returns the day of the month.

🎯 Scenario

advanced

Calculate the next working day excluding holidays

You have a start date in A1 and a list of holidays in range B1:B3. Which formula returns the next working day after the start date, skipping weekends and these holidays?

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

B =WORKDAY(A1, 1)

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

D =A1+1

💡 Hint

WORKDAY can take a holidays range to exclude those dates.

WORKDAY with the holidays argument skips weekends and the specified holidays. Option A skips weekends but not holidays. Option A uses WORKDAY.INTL with a weekend pattern but is unnecessary here. Option A just adds one day without skipping.

❓ data_analysis

expert

Count how many weekdays fall between two dates

You want to count how many weekdays (Monday to Friday) are between the dates in A1 (start) and B1 (end), inclusive. Which formula gives the correct count?

A =COUNTIF(WEEKDAY(ROW(INDIRECT(A1&":"&B1))), "<6")

B =NETWORKDAYS(A1, B1)

C =B1 - A1 + 1 - 2 * INT((B1 - A1 + 1)/7)

D =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)), 2) <= 5))

💡 Hint

There is a built-in function to count working days between two dates.

NETWORKDAYS counts weekdays between two dates, excluding weekends automatically. Option B is invalid because WEEKDAY cannot be applied directly to arrays like that. Option B is an approximation that fails for partial weeks. Option B uses SUMPRODUCT with ROW and WEEKDAY but is complex and error-prone.