Overview - WEEKDAY and WORKDAY

What is it?

WEEKDAY and WORKDAY are Excel functions that help you work with dates. WEEKDAY tells you which day of the week a date falls on, like Monday or Friday. WORKDAY calculates 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 guess which days are workdays, which is slow and error-prone. These functions save time and reduce mistakes when planning projects, tracking deadlines, or calculating business days. They help you focus on work, not on counting dates.

Where it fits

Before learning these, you should know how Excel stores dates as numbers and basic date formatting. After mastering these, you can explore more advanced date functions like NETWORKDAYS, EOMONTH, and custom scheduling formulas.

Mental Model

Core Idea

WEEKDAY tells you the day number of a date, and WORKDAY finds a future or past date counting only workdays, skipping weekends and holidays.

Think of it like...

Think of WEEKDAY as looking at a calendar to see what day it is, like checking if today is Monday or Friday. WORKDAY is like planning your work schedule by skipping weekends and holidays, so you only count the days you actually work.

Date (serial number) ──> WEEKDAY ──> Day number (1=Sunday to 7=Saturday)

Date + N workdays ──> WORKDAY ──> Future/Past date skipping weekends/holidays

┌─────────────┐      ┌─────────────┐
│ Input Date  │─────▶│ WEEKDAY     │
└─────────────┘      └─────────────┘

┌─────────────┐      ┌─────────────┐      ┌─────────────┐
│ Start Date  │─────▶│ WORKDAY     │─────▶│ Result Date │
│ Workdays N  │      │ (skip wknds)│      └─────────────┘
└─────────────┘      └─────────────┘

Build-Up - 7 Steps

1

FoundationUnderstanding Excel Dates as Numbers

Concept: Excel stores dates as numbers starting from January 1, 1900, which is 1.

In Excel, dates are not text but numbers. For example, January 1, 1900 is 1, January 2, 1900 is 2, and so on. This lets Excel do math with dates, like adding days or finding differences. You can format these numbers to show as dates.

Result

You can add 1 to a date number to get the next day, or subtract to find how many days between dates.

Knowing dates are numbers helps you understand how WEEKDAY and WORKDAY calculate results by working with these numbers.

2

FoundationUsing WEEKDAY to Find Day Numbers

3

IntermediateCustomizing WEEKDAY Numbering Styles

4

IntermediateCalculating Future Workdays with WORKDAY

5

IntermediateIncluding Holidays in WORKDAY Calculations

6

AdvancedUsing WEEKDAY and WORKDAY Together for Scheduling

7

ExpertLimitations and Edge Cases of WORKDAY Function

Under the Hood

Excel stores dates as serial numbers counting days from a base date. WEEKDAY extracts the day of the week by calculating the remainder when dividing the date number by 7, adjusted by the chosen numbering scheme. WORKDAY adds or subtracts days one by one, skipping dates that fall on weekends or holidays by checking their day numbers and holiday lists.

Why designed this way?

These functions were designed to simplify common business tasks like scheduling and deadline calculation. The fixed weekend days reflect common Western workweeks, and the holiday list allows customization. The design balances simplicity and flexibility for most users.

┌───────────────┐
│ Input Date    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ WEEKDAY Logic │
│ (mod 7 calc)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Day Number    │
└───────────────┘


┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│ Start Date    │─────▶│ WORKDAY Logic │─────▶│ Result Date   │
│ Days to Add   │      │ (skip wknds & │      └───────────────┘
│ Holidays List │      │  holidays)    │
└───────────────┘      └───────────────┘

Myth Busters - 4 Common Misconceptions

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

Common Belief:WEEKDAY always returns 1 for Sunday and counts up to 7 for Saturday.

Tap to reveal reality

Quick: Does WORKDAY skip all weekends automatically, no matter which days they are? Commit to yes or no.

Common Belief:WORKDAY skips any weekend days automatically, even if weekends are Friday-Saturday or other days.

Tap to reveal reality

Quick: Does WORKDAY count holidays as workdays by default? Commit to yes or no.

Common Belief:WORKDAY always skips holidays automatically without needing to specify them.

Tap to reveal reality

Quick: Can WEEKDAY return text like 'Monday' or 'Tuesday'? Commit to yes or no.

Common Belief:WEEKDAY returns the name of the day like 'Monday' or 'Tuesday'.

Tap to reveal reality

Expert Zone

1

WORKDAY.INTL allows defining custom weekend days using a 7-character string, enabling support for global workweek patterns.

2

WEEKDAY's numbering schemes can be combined with CHOOSE to map day numbers to localized day names or custom labels.

3

WORKDAY ignores time parts of dates, so partial days or times do not affect the calculation, which can surprise users expecting time-aware results.

When NOT to use

Avoid WORKDAY if your schedule includes half-days, variable work hours, or non-standard weekends without using WORKDAY.INTL. For hourly or minute-level scheduling, use date-time arithmetic or specialized scheduling software instead.

Production Patterns

Professionals use WORKDAY with holiday lists stored in separate sheets to dynamically calculate project deadlines. WEEKDAY combined with conditional formatting highlights weekends or specific weekdays in calendars. Advanced users create custom functions to handle international workweeks using WORKDAY.INTL.

Connections

Project Management Scheduling

WEEKDAY and WORKDAY build on the concept of working days used in project timelines.

Understanding these functions helps automate deadline calculations and resource planning in project management tools.

Business Calendar Systems

WORKDAY mimics business calendars by excluding weekends and holidays.

Knowing how WORKDAY works clarifies how business calendars handle non-working days and supports integration with calendar software.

Modular Arithmetic in Mathematics

WEEKDAY uses modular arithmetic to cycle through days of the week.

Recognizing WEEKDAY as a mod 7 operation connects spreadsheet date logic to fundamental math concepts.

Common Pitfalls

#1Using WEEKDAY without specifying numbering style causes confusion about which day is 1.

Wrong approach:=WEEKDAY(A1)

Correct approach:=WEEKDAY(A1, 2)

Root cause:Default WEEKDAY numbering starts on Sunday, which may not match user expectations or local week start.

#2Using WORKDAY without holiday list causes incorrect deadline calculations.

Wrong approach:=WORKDAY(A1, 10)

Correct approach:=WORKDAY(A1, 10, HolidaysRange)

Root cause:Holidays are not skipped unless explicitly provided, leading to counting non-working days as workdays.

#3Assuming WORKDAY handles non-standard weekends like Friday-Saturday off.

Wrong approach:=WORKDAY(A1, 5)

Correct approach:=WORKDAY.INTL(A1, 5, "0000110")

Root cause:WORKDAY only skips Saturday and Sunday; custom weekends require WORKDAY.INTL with a weekend pattern string.

Key Takeaways

WEEKDAY returns a number representing the day of the week for any date, with customizable numbering schemes.

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

Both functions rely on Excel's date serial number system to perform calculations efficiently.

Understanding their defaults and options prevents common scheduling errors, especially across different cultures and calendars.

Combining WEEKDAY and WORKDAY enables powerful date-based automation for business and project management.