Calculate Business Days with WORKDAY and NETWORKDAYS (Holidays, Weekends, and Custom Workdays)
D+N deadlines, monthly workdays, and project Gantt charts—use WORKDAY/NETWORKDAYS and INTL options to accurately reflect your company calendar.
Syntax and Key Options
| Function | Syntax | Key Point |
|---|---|---|
| WORKDAY | =WORKDAY(start_date, days, [holidays]) | Weekend is fixed as Saturday and Sunday |
| WORKDAY.INTL | =WORKDAY.INTL(start, days, [weekend], [holidays]) | Specify a weekend pattern (for example, 7 = Saturday only) |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Number of workdays between two dates |
| NETWORKDAYS.INTL | =NETWORKDAYS.INTL(start, end, [weekend], [holidays]) | Accounts for custom weekends and holidays |
Weekend code examples: 1 = Sunday and Saturday, 2 = Monday and Sunday, 7 = Saturday only. You can also use an 11-character string (Monday through Sunday; 1 = non-working day / 0 = workday). Example: "0000011" = Saturday and Sunday are non-working days only.
Calculate Deadlines — WORKDAY/WORKDAY.INTL
Example 1) 10 business days after the start date (D2)
=WORKDAY(D2, 10, HOL)Example 2) Friday–Saturday weekend calendar (Middle East)
=WORKDAY.INTL(D2, 7, 5, HOL) /* 5=Friday–Saturday */Example 3) 5 business days earlier
=WORKDAY(D2, -5, HOL)Count Workdays — NETWORKDAYS/INTL
Example 4) Workdays from start to end (E2:F2)
=NETWORKDAYS(E2, F2, HOL)Example 5) Saturday only as a non-working day (6-day workweek)
=NETWORKDAYS.INTL(E2, F2, 7, HOL)Example 6) Number of workdays per month (reference month in G2)
=NETWORKDAYS(EOMONTH(G2,-1)+1, EOMONTH(G2,0), HOL)8 Practical Patterns
① SLA based on issue registration date (D+3 business days)
=WORKDAY(A2, 3, HOL)② Elapsed workdays excluding weekends and holidays (through today)
=NETWORKDAYS(B2, TODAY(), HOL) - 1③ Gantt bar (color workdays only)
=--(NETWORKDAYS($B2, C$1, HOL) - NETWORKDAYS($A2-1, C$1, HOL) = 1)④ Sprint end date (custom weekend string)
=WORKDAY.INTL(A2, 9, "0000011", HOL) /* Saturday–Sunday non-working days */⑤ 9:00 AM on the next workday (date + time)
=WORKDAY(TODAY(),1,HOL) + TIME(9,0,0)⑥ Work only on specific weekdays (Monday–Thursday workdays, Friday–Sunday non-working days)
=NETWORKDAYS.INTL(E2, F2, "0000111", HOL)⑦ Dynamic holiday filter (include holidays from the relevant month only)
=NETWORKDAYS(E2, F2, FILTER(HOL, TEXT(HOL,"yyyymm")=TEXT(E2,"yyyymm")))⑧ Work backward by “business days +N” (move up a delivery date)
=WORKDAY(INT(F2), -N, HOL)Common Mistakes and Checks
- Text dates → Convert them to date format (using formatting or
DATEVALUE). - Holiday list range → Avoid merged cells and blanks; update the list when the year changes.
- Confusing weekend codes → Check the INTL code against local conventions.
- Cells containing times → Use
INT()to work with the date only, then add the time.
Summary
| Goal | Typical Formula |
|---|---|
| Deadline N business days later | WORKDAY(start, N, HOL) |
| Number of workdays in a period | NETWORKDAYS(start, end, HOL) |
| Custom weekend | WORKDAY/NETWORKDAYS.INTL(..., weekend, HOL) |