Calculate Business Days with WORKDAY and NETWORKDAYS (INTL, Weekends, and Holidays)
“Delivery date 7 business days from now” or “deadline on the last business day of this month”—instead of checking a calendar, calculate it automatically with WORKDAY/NETWORKDAYS. You can also handle custom weekends and exclude holidays in one step.
Key concepts and syntax
| Function | Syntax | Description |
|---|---|---|
| WORKDAY | =WORKDAY(start_date, days, [holidays]) | Returns the date N business days after the start date (or before it if negative), excluding weekends (Saturday and Sunday) and holidays |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Returns the number of business days between two dates, inclusive |
| WORKDAY.INTL | =WORKDAY.INTL(start_date, days, [weekend], [holidays]) | Lets you specify the weekend pattern directly |
| NETWORKDAYS.INTL | =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) | Lets you specify the weekend pattern directly |
TIP: The result is a date value. Change only the display format with TEXT(, "yyyy-mm-dd").
N business days after or before — WORKDAY
Example 1) Estimated delivery date: ship date + 7 business days
=WORKDAY(A2, 7, $H$2:$H$30)Example 2) Deadline – 3 business days (advance preparation date)
=WORKDAY(B2, -3, $H$2:$H$30)Number of business days — NETWORKDAYS
Example 3) Total business days in a contract period
=NETWORKDAYS(A2, B2, $H$2:$H$30)Note If the start and end dates are business days, both are included.
Custom weekends — WORKDAY/NETWORKDAYS.INTL
Specify weekends with a numeric code (for example, 1 = Saturday–Sunday and 7 = Friday–Saturday) or a seven-character string (Monday through Sunday, where 1 = nonworking day and 0 = working day).
Example 4) Friday–Saturday weekend (Middle East)
=WORKDAY.INTL(A2, 5, 7, $H$2:$H$30)Example 5) Sunday only is a nonworking day (custom string)
=NETWORKDAYS.INTL(A2, B2, "0000001", $H$2:$H$30)7 practical templates
① Last business day of this month
=WORKDAY(EOMONTH(TODAY(),1), -1, $H$2:$H$30)② First business day of next month
=WORKDAY(EOMONTH(TODAY(),0), 1, $H$2:$H$30)③ Business-day SLA (10 business days)
=WORKDAY(A2, 10, $H$2:$H$30)④ Weekend pattern for a specific country (Sunday–Monday weekend)
=NETWORKDAYS.INTL(A2, B2, 2, $H$2:$H$30) // 2 = Sunday–Monday⑤ Use a dynamic range for the holiday table
=WORKDAY(A2, 7, Holidays[Date]) // Excel table reference⑥ Four-day workweek custom setting (Friday off; assumes Saturday and Sunday are working days)
=NETWORKDAYS.INTL(A2, B2, "0000100") // Friday only is 1 (nonworking day)⑦ Remaining business days for a Gantt chart
=NETWORKDAYS(TODAY(), E2, $H$2:$H$30)Common mistakes and checklist
- Text dates → Convert them to numeric dates with
DATEVALUE. - Holiday range → Always use an absolute reference (
$H$2:$H$30) or a table reference. - Weekend string order → Do not use
"1234567"; use a seven-character Monday-through-Sunday string (for example, Sunday only off is"0000001"). - Separate display from calculation → The result is a date; use
TEXTonly for display. Do not calculate again using a TEXT result.
Summary
| Goal | Typical formula |
|---|---|
| N business days after or before | WORKDAY(start, days, holidays) |
| Number of business days | NETWORKDAYS(start, end, holidays) |
| Custom weekends | WORKDAY/NETWORKDAYS.INTL(..., weekend) |
| Last or first business day | WORKDAY(EOMONTH(...), ±1, holidays) |
FAQ
How is the weekend string interpreted?
It is a seven-character binary string in Monday-through-Sunday order, where 1 = nonworking day and 0 = working day. For example, "0000011" means only Saturday and Sunday are nonworking days, which is the default weekend.
Can I count backward from the end?
Not directly. Instead, change the reference point and use negative days with WORKDAY, or reverse the date range with NETWORKDAYS.