Overview - WEEKDAY and WORKDAY

What is it?

WEEKDAY and WORKDAY are two useful functions in spreadsheets that help you work with dates. WEEKDAY tells you which day of the week a date falls on, like Monday or Friday. WORKDAY helps you find a date that is a certain number of working days away, skipping weekends and optionally holidays. These functions make managing schedules and deadlines easier.

Why it matters

Without WEEKDAY and WORKDAY, you would have to manually count days or check calendars to find weekdays or workdays. This is slow and error-prone, especially for long periods or when holidays matter. These functions save time and reduce mistakes in planning, payroll, project management, and more.

Where it fits

Before learning these, you should understand basic date formats and simple date arithmetic in spreadsheets. After mastering WEEKDAY and WORKDAY, you can explore more advanced date functions like NETWORKDAYS, EOMONTH, and custom holiday lists for better scheduling.

Mental Model

Core Idea

WEEKDAY tells you the day number of a date in the week, and WORKDAY calculates future or past dates counting only working days, skipping weekends and holidays.

Think of it like...

Think of WEEKDAY as looking at a calendar to see which day of the week a date is, like checking if a meeting is on Tuesday. WORKDAY is like planning your work schedule by skipping weekends and holidays, just like you wouldn’t plan a meeting on a Saturday.

Date Input ──▶ WEEKDAY ──▶ Day Number (1=Sunday to 7=Saturday)

Date Input + Days ──▶ WORKDAY ──▶ Future/Past Workday Date (skips weekends/holidays)

Build-Up - 7 Steps

1

FoundationUnderstanding Date Formats in Sheets

Concept: Dates are stored as numbers in spreadsheets, which lets us do math with them.

In Google Sheets, dates are numbers starting from December 30, 1899. For example, January 1, 2024 is stored as 45175. You can add or subtract numbers to move between dates. For example, =A1+1 moves one day forward.

Result

You can see that adding 1 to a date moves to the next day.

Understanding that dates are numbers lets you use math and functions like WEEKDAY and WORKDAY effectively.

2

FoundationBasic Use of WEEKDAY Function

3

IntermediateCustomizing WEEKDAY Numbering

4

IntermediateUsing WORKDAY to Skip Weekends

5

IntermediateIncluding Holidays in WORKDAY Calculations

6

AdvancedCombining WEEKDAY and WORKDAY for Scheduling

7

ExpertHandling Non-Standard Weekends with WORKDAY.INTL

Under the Hood

Internally, WEEKDAY converts the date number to a day index by calculating the remainder when dividing by 7, adjusted by the chosen start day. WORKDAY counts days forward or backward, skipping dates identified as weekends or holidays by checking each date’s weekday number and comparing against holiday lists.

Why designed this way?

These functions were designed to simplify common date tasks in business and personal planning. WEEKDAY provides a simple numeric day code for easy logic. WORKDAY automates skipping non-working days, a frequent manual task prone to errors. The design balances simplicity with flexibility by allowing optional parameters for numbering and holidays.

┌─────────────┐       ┌───────────────┐
│   Date #    │──────▶│ WEEKDAY Logic │─────▶ Day Number
└─────────────┘       └───────────────┘

┌─────────────┐       ┌───────────────┐       ┌───────────────┐
│ Start Date  │──────▶│ WORKDAY Count │──────▶│ Check Weekend │
│ Days to Add │       │ (skip weekends)│      │ & Holidays    │
└─────────────┘       └───────────────┘       └───────────────┘
                                         │
                                         ▼
                                  Result Date

Myth Busters - 4 Common Misconceptions

Quick: Does WEEKDAY always start counting from Sunday as 1? Commit to yes or no.

Common Belief:WEEKDAY always counts Sunday as day 1 and Saturday as day 7.

Tap to reveal reality

Quick: Does WORKDAY count holidays automatically if you don’t list them? Commit to yes or no.

Common Belief:WORKDAY automatically knows all public holidays and skips them without input.

Tap to reveal reality

Quick: Can WORKDAY handle weekends other than Saturday and Sunday without extra parameters? Commit to yes or no.

Common Belief:WORKDAY always skips Saturday and Sunday only, no exceptions.

Tap to reveal reality

Quick: Does WEEKDAY return the weekday name like 'Monday' or just a number? Commit to yes or no.

Common Belief:WEEKDAY returns the weekday name directly.

Tap to reveal reality

Expert Zone

1

WORKDAY and WEEKDAY rely on the spreadsheet’s date system, so incorrect date formats or locale settings can cause subtle bugs.

2

Using WORKDAY.INTL with custom weekend strings requires careful mapping of days to the 7-character code, which can be confusing but powerful.

3

Combining WEEKDAY with conditional formatting or array formulas can create dynamic schedules that update automatically with date changes.

When NOT to use

Avoid WORKDAY if you need to count all calendar days including weekends and holidays; use simple date addition instead. For complex calendars with irregular holidays or partial workdays, consider custom scripts or add-ons. WEEKDAY is not suitable if you want localized weekday names directly; use TEXT with custom formats instead.

Production Patterns

In real-world use, WORKDAY is common in payroll systems to calculate pay periods and deadlines. WEEKDAY is used in scheduling apps to highlight weekends or trigger alerts on specific weekdays. Advanced users combine these with holiday tables and dynamic ranges to automate business calendars.

Connections

Conditional Formatting

Builds-on

Knowing WEEKDAY helps create rules that highlight weekends or specific weekdays automatically in calendars.

Project Management

Same pattern

WORKDAY’s logic mirrors how project managers count working days to set realistic deadlines, skipping non-working days.

Business Operations

Builds-on

Understanding WORKDAY supports accurate payroll and shift scheduling, critical for smooth business operations.

Common Pitfalls

#1Adding days to a date without skipping weekends causes wrong workday results.

Wrong approach:=A1 + 5 // Adds 5 calendar days, includes weekends

Correct approach:=WORKDAY(A1, 5) // Adds 5 workdays, skips weekends

Root cause:Confusing calendar days with workdays leads to incorrect scheduling.

#2Not providing holidays to WORKDAY causes scheduling on holidays.

Wrong approach:=WORKDAY("2024-12-20", 5) // Ignores holidays

Correct approach:=WORKDAY("2024-12-20", 5, HolidaysRange) // Skips holidays

Root cause:Assuming WORKDAY knows holidays by default causes missed holiday skips.

#3Using WEEKDAY without specifying type leads to wrong weekday numbering for some locales.

Wrong approach:=WEEKDAY(A1) // Sunday=1 always

Correct approach:=WEEKDAY(A1, 2) // Monday=1, matches many business weeks

Root cause:Not customizing WEEKDAY numbering causes logic errors in weekday-based formulas.

Key Takeaways

WEEKDAY returns a number representing the day of the week for any date, with customizable numbering to fit your week start.

WORKDAY calculates future or past dates counting only working days, automatically skipping weekends and optionally holidays.

Including holidays in WORKDAY makes scheduling accurate for real-world business calendars.

WORKDAY.INTL extends WORKDAY by allowing custom weekend definitions for global or special cases.

Combining WEEKDAY and WORKDAY enables powerful date calculations for scheduling, planning, and automation.