0
0
Google Sheetsspreadsheet~8 mins

WEEKDAY and WORKDAY in Google Sheets - Dashboard Guide

Choose your learning style9 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.