Workday Function in Excel: A Practical Guide for Date Calculations
Learn how the workday function in excel works, with practical examples, holiday handling, and advanced patterns like WORKDAY.INTL for custom weekends. Build reliable calendars, due dates, and schedules without manual counting.
The workday function in excel calculates a date offset by a specified number of working days from a start date. By default it excludes Saturdays and Sundays and can incorporate an optional holidays list. This makes it ideal for due dates, payroll planning, and project timelines that must skip non-working days. Use WORKDAY for standard weekends and WORKDAY.INTL for custom patterns.
What the workday function in excel does and when to use it
The workday function in excel calculates a date offset by a specified number of working days from a start date. By default, it excludes Saturdays and Sundays and can also ignore dates listed as holidays. This is invaluable for creating realistic schedules, due dates, payroll cycles, and project milestones that must skip non-working days. Use it when you need a date that represents the next business day after X workdays, not just a simple calendar day.
=WORKDAY(TODAY(), 5)This returns the date five workdays from today, excluding weekends. You can switch to a static start date:
=WORKDAY("2026-04-01", 10, Holidays!$A$2:$A$10)In this example, Excel adds 10 workdays to April 1, 2026, while excluding any dates in the Holidays range. For more control over weekends, use WORKDAY.INTL:
=WORKDAY.INTL(A2, B2, "0000011", Holidays!$A$2:$A$10)Here weekend pattern "0000011" marks Saturday and Sunday as non-working days, while other days are counted as workdays. The function takes:
- start_date
- days
- holidays (optional)
You can also reference a dynamic named range for holidays to keep formulas maintainable. For more variations, see the related WORKDAY.INTL function.
Practical workflows: due dates, payroll, and date ranges
In practice, the workday function in excel helps you compute due dates without manually counting days. For example:
=WORKDAY(DATE(2026,4,1), 7)This returns a date one week from April 1, 2026, skipping weekends. If you need to honor a specific weekend pattern (like Friday-Sunday off), use:
=WORKDAY.INTL(DATE(2026,4,1), 7, "0000110", Holidays!$A$2:$A$10)You can also combine with TODAY() to create dynamic deadlines:
=WORKDAY(TODAY(), 3)When building larger schedules, referencing a date cell and a numeric days cell is common:
=WORKDAY(A2, B2, Holidays!$A$2:$A$10)This approach makes your workbook responsive to user inputs. If you want to count business days between two dates, you may supplement with NETWORKDAYS.INTL for a complete analytics view, understanding that WORKDAY focuses on adding days rather than counting from start to finish.
Handling holidays and custom weekends with WORKDAY.INTL
The WORKDAY.INTL function extends the basics by letting you specify a weekend pattern and a holiday list:
=WORKDAY.INTL("2026-04-01", 15, "0000011", Holidays!$A$2:$A$30)The pattern string "0000011" marks Saturday and Sunday as non-working days; other days count as workdays. You can also define a fully custom weekend with a binary pattern, where 1 = weekend and 0 = workday. For readability, store holidays in a named range (e.g., Holidays) and reuse it across formulas:
=WORKDAY.INTL(StartDate, Days, "1111100", Holidays)If your organization uses an alternate week off (e.g., Friday), adjust the pattern accordingly and test with a few sample dates to ensure accuracy.
Common pitfalls and troubleshooting
Common mistakes include feeding text dates or non-numeric day counts:
=WORKDAY("2026-04-01", "5") // incorrect: days must be numericAlways convert inputs:
=WORKDAY(DATE(2026,4,1), 5)Another pitfall is forgetting the holidays parameter, which can push shifts onto weekends or holidays. If you want to hide errors when a start date is blank, use a simple guard:
=IF(A2="","", WORKDAY(A2, B2, Holidays!$A$2:$A$10))Also ensure your system date formats align with your workbook locale to avoid misinterpretation of dates.
Performance tips and version notes
In Excel 365, you can simplify long formulas with LET and dynamic arrays:
=LET(start, DATE(2026,4,1),
days, 20,
WORKDAY(start, days, Holidays!$A$2:$A$50))LET assigns intermediate values, making formulas easier to read and edit. If you maintain a large holiday list, consider using a dynamic named range that expands automatically as you add dates:
Holidays := OFFSET(HolidaysBase, 0, 0, COUNTA(HolidaysBase[Date]), 1)For older versions, avoid LET and keep formulas concise. Always verify results with a few manual checks, especially when holidays cross year boundaries.
Real-world patterns and alternatives
For teams tracking sprint dates, you might compute milestones as:
=WORKDAY.INTL(StartDate, 8, "0000011", Holidays!$A$2:$A$10)Alternatively, when you only need a date count, NETWORKDAYS.INTL can help you count business days between two dates:
=NETWORKDAYS.INTL(StartDate, EndDate, "0000011", Holidays!$A$2:$A$10)Remember, WORKDAY returns a date that you can format, subtract, or combine with other date functions to assemble reliable calendars and schedules.
Steps
Estimated time: 60-90 minutes
- 1
Set up your dates
Choose a starting date in a cell or use a function like DATE or TODAY to generate it. Clearly label the cell (e.g., StartDate) for readability.
Tip: Label inputs and use named ranges for clarity. - 2
Define workdays to add
Enter the number of workdays you want to add in a separate cell (e.g., DaysToAdd). This keeps your formula modular.
Tip: Keep DaysToAdd as a numeric value, not text. - 3
Create a holidays list
List all holiday dates in a contiguous range and optionally name the range (e.g., Holidays). This ensures holidays are excluded.
Tip: Use a separate sheet to keep holidays organized. - 4
Write the core WORKDAY formula
Combine start date, days, and holidays using either WORKDAY or WORKDAY.INTL for custom weekends.
Tip: Test with a few sample dates to confirm weekends are treated as expected. - 5
Extend to multiple rows
Fill down the formula to a table, ensuring each row references its own StartDate and DaysToAdd, with Holidays kept in a named range.
Tip: Use absolute/relative references appropriately for stability. - 6
Validate and document
Cross-check a few results with manual date counting and document assumptions (weekend pattern and holidays list).
Tip: Add comments or notes in the workbook to explain logic.
Prerequisites
Required
- Required
- Basic knowledge of date functions (TODAY, DATE)Required
- A test workbook to practice the examplesRequired
Optional
- Access to a holidays list (optional but recommended)Optional
- A named range for Holidays (optional but recommended)Optional
- Familiarity with named ranges and simple IF statementsOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy formula or value | Ctrl+C |
| PastePaste formula or value | Ctrl+V |
| SaveSave workbook | Ctrl+S |
| UndoUndo last action | Ctrl+Z |
| Fill DownFill selected cell downward | Ctrl+D |
| Fill RightFill selected cell to the right | Ctrl+R |
People Also Ask
What is the difference between WORKDAY and WORKDAY.INTL?
WORKDAY uses Saturday and Sunday as weekends by default and allows a holidays list. WORKDAY.INTL adds a weekend pattern you specify, enabling non-Saturday/Sunday weekends and fully custom schedules.
WORKDAY uses standard weekends, while WORKDAY.INTL lets you define any weekend pattern, making it more flexible for custom calendars.
Can WORKDAY return past dates?
Yes. If you provide a negative Days value, WORKDAY returns a date in the past relative to the start date.
Yes. If you put a negative number of days, it moves backward in time.
How do I exclude holidays from the result?
Include a holidays range in the formula, e.g., =WORKDAY(start, days, Holidays). This ensures holiday dates are skipped in addition to weekends.
Just pass a list of holidays to skip, and Excel will avoid those days too.
Is WORKDAY available on Excel for Mac?
Yes. Both Windows and Mac versions of Excel support WORKDAY and WORKDAY.INTL with similar syntax.
Absolutely. You can use these functions on Mac as well.
What should I do for a Friday-based weekend?
Use WORKDAY.INTL with a custom weekend pattern, such as '0000100' if Friday is off, and verify with a few sample dates.
Set a custom weekend pattern that marks Friday as non-working and test a few dates to confirm.
The Essentials
- Use WORKDAY to add business days by default
- Switch to WORKDAY.INTL for custom weekends
- Always pass a holidays range when needed
- Test formulas with sample dates to verify behavior