Bird
Raised Fist0
Google Sheetsspreadsheet~8 mins

WEEKDAY and WORKDAY in Google Sheets - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - WEEKDAY and WORKDAY
Dashboard Goal

Help a small business owner track delivery dates and understand which days of the week deliveries fall on, excluding weekends and holidays.

Sample Data
Order IDOrder DateDelivery DaysHoliday
10012024-06-0352024-06-10
10022024-06-0432024-06-10
10032024-06-0572024-06-10
10042024-06-0642024-06-10
10052024-06-0762024-06-10
Dashboard Components
  • Delivery Weekday (Column E): Shows the day of the week for the order date.
    Formula example for E2: =WEEKDAY(B2, 2)
    Output: Number 1 (Monday) to 7 (Sunday), where 1 = Monday.
  • Expected Delivery Date (Column F): Calculates the delivery date excluding weekends and the holiday.
    Formula example for F2: =WORKDAY(B2, C2, D2:D6)
    Output: Date after adding delivery days skipping weekends and holiday on 2024-06-10.
  • Delivery Day Name (Column G): Converts the delivery date to a weekday name.
    Formula example for G2: =TEXT(F2, "dddd")
    Output: Full weekday name like "Monday" or "Tuesday".
Dashboard Layout
+-------------------------------------------------+
|                 Delivery Orders                  |
| +---------+------------+--------------+---------+|
| | OrderID | Order Date | Delivery Days| Holiday |
| +---------+------------+--------------+---------+|
| | 1001    | 2024-06-03 | 5            |2024-06-10|
| | 1002    | 2024-06-04 | 3            |2024-06-10|
| | 1003    | 2024-06-05 | 7            |2024-06-10|
| | 1004    | 2024-06-06 | 4            |2024-06-10|
| | 1005    | 2024-06-07 | 6            |2024-06-10|
| +---------+------------+--------------+---------+|
| Delivery Weekday | Expected Delivery Date | Day Name |
|       (E)       |          (F)           |   (G)    |
+-------------------------------------------------+
Interactivity

The holiday date in column D (D2:D6) acts as a dynamic holiday list for the WORKDAY function. Changing the holiday date updates all expected delivery dates automatically, showing how delivery schedules adjust around holidays.

Changing the order date or delivery days updates the weekday and expected delivery date accordingly.

Self Check

If you change the holiday date from 2024-06-10 to 2024-06-07, which delivery dates change? Which weekday names update?

Key Result
Dashboard shows order weekdays and calculates delivery dates excluding weekends and holidays using WEEKDAY and WORKDAY functions.

Practice

(1/5)
1. What does the WEEKDAY function return when given a date in Google Sheets?
easy
A. The name of the month for that date
B. The date of the next working day
C. The number of days between two dates
D. A number representing the day of the week for that date

Solution

  1. Step 1: Understand the purpose of WEEKDAY

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

  2. Step 2: Compare options with function behavior

    Only a number representing the day of the week for that date correctly describes this behavior; others describe unrelated functions.

  3. Final Answer:

    A number representing the day of the week for that date -> Option D

  4. Quick Check:

    WEEKDAY = day number [OK]
Hint: WEEKDAY gives day number, not names or intervals [OK]
Common Mistakes:
  • Thinking WEEKDAY returns the month's name
  • Confusing WEEKDAY with date difference functions
  • Assuming WEEKDAY returns the next workday
2. Which of the following is the correct syntax to add 5 working days to a date in cell A1, skipping weekends in Google Sheets?
easy
A. =WEEKDAY(A1 + 5)
B. =WORKDAY(A1, 5)
C. =WORKDAY(5, A1)
D. =WORKDAY(A1 + 5)

Solution

  1. Step 1: Recall WORKDAY function syntax

    WORKDAY(start_date, days, [holidays]) adds working days to start_date, skipping weekends.

  2. Step 2: Check each option's syntax

    =WORKDAY(A1, 5) correctly uses WORKDAY with start_date A1 and 5 days. Others misuse argument order or functions.

  3. Final Answer:

    =WORKDAY(A1, 5) -> Option B

  4. Quick Check:

    WORKDAY(start_date, days) [OK]
Hint: WORKDAY(start_date, days) adds workdays skipping weekends [OK]
Common Mistakes:
  • Swapping arguments in WORKDAY
  • Using WEEKDAY instead of WORKDAY to add days
  • Adding days directly without WORKDAY
3. If cell A1 contains the date 2024-06-14 (a Friday), what will =WORKDAY(A1, 3) return?
medium
A. 2024-06-19 (Wednesday)
B. 2024-06-18 (Tuesday)
C. 2024-06-17 (Monday)
D. 2024-06-20 (Thursday)

Solution

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

    Start date is Friday, June 14, 2024. We add 3 working days skipping weekends.

  2. Step 2: Count 3 working days skipping weekend

    Day 1: Monday, June 17
    Day 2: Tuesday, June 18
    Day 3: Wednesday, June 19

  3. Final Answer:

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

  4. Quick Check:

    WORKDAY skips weekends, so 3 workdays after Friday is Wednesday [OK]
Hint: Count workdays skipping Sat/Sun after start date [OK]
Common Mistakes:
  • Counting weekend days as workdays
  • Adding days directly without WORKDAY
  • Misidentifying the weekday of the start date
4. The formula =WORKDAY(A1, 10, C1:C3) returns an error. What is the most likely cause?
medium
A. WORKDAY cannot accept a range for holidays
B. The second argument must be a date, not a number
C. The holidays range C1:C3 contains invalid dates
D. The start date in A1 is not a valid date

Solution

  1. Step 1: Understand WORKDAY arguments

    WORKDAY(start_date, days, [holidays]) expects holidays as valid dates in a range.

  2. Step 2: Identify common error with holidays

    If holidays range contains invalid or text values, WORKDAY returns an error.

  3. Final Answer:

    The holidays range C1:C3 contains invalid dates -> Option C

  4. Quick Check:

    Invalid holiday dates cause WORKDAY errors [OK]
Hint: Check holiday dates for validity if WORKDAY errors [OK]
Common Mistakes:
  • Assuming days argument must be a date
  • Thinking WORKDAY can't accept holiday ranges
  • Ignoring invalid or text values in holidays
5. You want to find the weekday number of the date that is 7 working days after 2024-06-10, excluding weekends and the holidays in range B1:B2. Which formula correctly does this?
hard
A. =WEEKDAY(WORKDAY(DATE(2024,6,10), 7, B1:B2))
B. =WORKDAY(WEEKDAY(DATE(2024,6,10)), 7, B1:B2)
C. =WEEKDAY(DATE(2024,6,10) + 7)
D. =WORKDAY(DATE(2024,6,10), 7) + WEEKDAY(B1:B2)

Solution

  1. Step 1: Calculate the date 7 working days after June 10, 2024

    Use WORKDAY with start date DATE(2024,6,10), days 7, and holidays B1:B2 to skip weekends and holidays.

  2. Step 2: Find the weekday number of the resulting date

    Wrap the WORKDAY result inside WEEKDAY to get the day number of that date.

  3. Final Answer:

    =WEEKDAY(WORKDAY(DATE(2024,6,10), 7, B1:B2)) -> Option A

  4. Quick Check:

    WORKDAY then WEEKDAY gives weekday number after skipping holidays [OK]
Hint: Use WORKDAY inside WEEKDAY to get weekday number after workdays [OK]
Common Mistakes:
  • Using WEEKDAY before WORKDAY
  • Adding days directly ignoring holidays
  • Trying to add WEEKDAY results to dates
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard