Eric Campbell 5 min read Excel

How to Use WORKDAY in Excel

The WORKDAY function solves a problem every project manager and business analyst faces: calculating dates while respecting business calendars. When you tell a client 'we'll deliver in 10 business...

Key Insights

  • WORKDAY calculates future or past dates while automatically excluding weekends and holidays, making it essential for project management and SLA tracking in business contexts.
  • WORKDAY.INTL extends the base function with 17 weekend configurations plus custom patterns, accommodating international work schedules like Middle Eastern Friday-Saturday weekends.
  • Combining WORKDAY with named ranges for holidays and IFERROR for validation creates robust, maintainable date calculation systems that handle edge cases gracefully.

Introduction to WORKDAY Function

The WORKDAY function solves a problem every project manager and business analyst faces: calculating dates while respecting business calendars. When you tell a client “we’ll deliver in 10 business days,” you can’t just add 10 to today’s date. You need to skip weekends and holidays, and doing this manually is error-prone and time-consuming.

WORKDAY automates this calculation. Give it a start date and a number of working days, and it returns the resulting date while automatically excluding Saturdays and Sundays. Add an optional list of holidays, and you have a powerful tool for deadline tracking, project planning, service level agreements (SLAs), and delivery scheduling.

The basic syntax looks like this:

=WORKDAY(start_date, days, [holidays])
  • start_date: The date you’re counting from
  • days: Number of working days to add (positive) or subtract (negative)
  • [holidays]: Optional range of dates to exclude

Basic WORKDAY Syntax and Simple Examples

Let’s start with straightforward applications. Suppose you receive an order on January 15, 2024, and promise delivery in 10 business days. Here’s how you calculate the delivery date:

=WORKDAY(DATE(2024,1,15), 10)

This returns January 29, 2024, accounting for two intervening weekends. If you’re working with dates already in cells, the formula becomes even cleaner:

=WORKDAY(A2, 10)

Where A2 contains your order date. This is the pattern you’ll use most often in real spreadsheets.

For a project management scenario, imagine tracking multiple milestones. You have a project start date in cell B2 and want to calculate when different phases complete:

// Phase 1 (5 business days)
=WORKDAY(B2, 5)

// Phase 2 (15 business days from Phase 1 completion)
=WORKDAY(C2, 15)

// Phase 3 (8 business days from Phase 2 completion)
=WORKDAY(D2, 8)

This creates a cascading timeline where each phase builds on the previous one, all while respecting weekends automatically.

Adding Holidays to WORKDAY Calculations

Weekends are predictable, but holidays vary by country, company, and even department. WORKDAY’s third parameter handles this by accepting a range of dates to exclude.

First, create your holiday list somewhere in your workbook. I recommend a dedicated sheet or table:

// In a range named "CompanyHolidays"
1/1/2024   // New Year's Day
1/15/2024  // Martin Luther King Jr. Day
2/19/2024  // Presidents' Day
5/27/2024  // Memorial Day
7/4/2024   // Independence Day
9/2/2024   // Labor Day
11/28/2024 // Thanksgiving
12/25/2024 // Christmas

Now reference this range in your WORKDAY formula:

=WORKDAY(A2, 10, CompanyHolidays)

If you haven’t created a named range, you can reference the cells directly:

=WORKDAY(A2, 10, Holidays!$A$2:$A$9)

The dollar signs create absolute references so the formula copies correctly when you drag it down.

For dynamic holiday management using Excel tables, convert your holiday list to a table (Ctrl+T) and reference it:

=WORKDAY(A2, 10, HolidayTable[HolidayDate])

This approach is superior because adding holidays to the table automatically updates all formulas that reference it. No need to adjust ranges or update formulas across multiple sheets.

WORKDAY.INTL for Custom Weekends

WORKDAY assumes Saturday and Sunday are weekends, but that’s not universal. Many Middle Eastern countries use Friday-Saturday weekends. Some companies operate Tuesday-Saturday schedules. WORKDAY.INTL handles these scenarios.

The syntax adds a weekend parameter:

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

The weekend parameter accepts codes 1-17 for predefined patterns or a custom string. Here are the most useful codes:

  • 1: Saturday-Sunday (default, same as WORKDAY)
  • 7: Friday-Saturday (Middle East standard)
  • 11: Sunday only
  • 12: Monday only
  • 13: Tuesday-Wednesday

For a Friday-Saturday weekend:

=WORKDAY.INTL(A2, 10, 7, CompanyHolidays)

The custom string format gives you complete control. Use seven characters, where 1 means weekend and 0 means workday, starting with Monday:

// Tuesday-Wednesday off (0011000)
=WORKDAY.INTL(A2, 10, "0011000", CompanyHolidays)

// Only work Monday-Wednesday (0001111)
=WORKDAY.INTL(A2, 10, "0001111", CompanyHolidays)

This flexibility is crucial for international businesses or companies with unconventional schedules.

Calculating Backwards with Negative Days

WORKDAY works in reverse too. If you have a deadline and need to know when to start, use negative days:

// Deadline is in A2, need 15 business days to complete
=WORKDAY(A2, -15, CompanyHolidays)

This calculates the latest possible start date to meet your deadline. It’s invaluable for project planning when you’re working backward from a fixed delivery date.

A practical example: You’re launching a product on December 1st and need 20 business days for final testing. When must testing begin?

=WORKDAY(DATE(2024,12,1), -20, CompanyHolidays)

This accounts for Thanksgiving and any other November holidays in your list, giving you the actual start date needed.

Common Errors and Troubleshooting

WORKDAY errors typically fall into three categories:

#VALUE! Error: Usually caused by non-date values or text that Excel can’t interpret as dates. Check that your start_date and holidays are formatted as dates, not text.

#NUM! Error: Occurs when the calculation results in an invalid date (before January 1, 1900, or after December 31, 9999). This happens with extreme day values or when calculating far into the past.

Unexpected Results: If dates seem wrong, verify your holiday list doesn’t contain duplicates or text values, and ensure date formats are consistent.

Wrap WORKDAY in IFERROR to handle edge cases gracefully:

=IFERROR(WORKDAY(A2, 10, CompanyHolidays), "Invalid Date")

For more sophisticated error handling:

=IF(ISBLANK(A2), "", IFERROR(WORKDAY(A2, 10, CompanyHolidays), "Check Date Format"))

This returns blank if the start date is empty, calculates normally if valid, and shows a helpful message if something’s wrong.

Practical Real-World Applications

Let’s build complete solutions that combine WORKDAY with other Excel functions.

Project Timeline Calculator with Milestones:

// Assuming: A2=Project Start, B2=Design Days, C2=Dev Days, D2=Testing Days
Design Complete:    =WORKDAY(A2, B2, Holidays)
Development Complete: =WORKDAY(E2, C2, Holidays)
Testing Complete:   =WORKDAY(F2, D2, Holidays)

SLA Compliance Tracker:

// A2=Ticket Created, B2=SLA Days, C2=Ticket Resolved
Due Date: =WORKDAY(A2, B2, Holidays)
Status: =IF(C2="", IF(TODAY()>D2, "OVERDUE", "OPEN"), IF(C2<=D2, "MET", "MISSED"))

Automated Delivery Estimator with Conditional Holidays:

// Different regions have different holidays
// A2=Order Date, B2=Region (US/UK/EU), C2=Processing Days

Delivery Date: =WORKDAY(A2, C2, 
    IF(B2="US", USHolidays, 
    IF(B2="UK", UKHolidays, 
    IF(B2="EU", EUHolidays, ""))))

This formula selects the appropriate holiday list based on region, ensuring accurate delivery estimates for international customers.

For a comprehensive order management system, combine everything:

// Order received: A2
// Processing time: B2 (business days)
// Region: C2
// Order priority: D2 (Standard/Rush)

Estimated Delivery: 
=WORKDAY.INTL(A2, 
    IF(D2="Rush", B2*0.5, B2), 
    IF(C2="Middle East", 7, 1),
    INDIRECT(C2&"Holidays"))

This accounts for rush orders (half the normal time), regional weekend patterns, and region-specific holidays—all in one formula.

WORKDAY transforms date calculations from manual, error-prone processes into automated, reliable systems. Master it, and you’ll handle project timelines, SLA tracking, and delivery scheduling with confidence. The key is building reusable holiday lists, understanding WORKDAY.INTL for international scenarios, and combining these functions with error handling for production-ready spreadsheets.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.