How to Use the NETWORKDAYS and NETWORKDAYS.INTL Functions in Google Sheets to Calculate Working Days

Calculating the true duration of a project, employee payroll period, or delivery schedule requires filtering out non-working days. While subtracting two dates in Google Sheets (such as =B2-A2) gives you total elapsed calendar days, it fails to account for weekends or statutory holidays. To solve this, Google Sheets provides two essential date functions: NETWORKDAYS and NETWORKDAYS.INTL. Whether your organisation observes traditional Saturday-Sunday weekends or custom work schedules across international offices, these functions automate accurate workday calculations.

Syntax and Argument Breakdown

Understanding the difference between the standard and international variants ensures you select the correct formula for your regional schedule:

1. NETWORKDAYS Syntax

=NETWORKDAYS(start_date, end_date, [holidays])

• start_date: The initial date from which the calculation starts (inclusive).
• end_date: The final date of the calculation period (inclusive).
• [holidays] (Optional): A cell range or array literal containing holiday dates to exclude from the working count.
• Note: Standard NETWORKDAYS permanently hardcodes weekends as Saturday and Sunday.

2. NETWORKDAYS.INTL Syntax

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

• [weekend] (Optional): A number or a 7-character binary string specifying which days of the week are considered non-working days.
• If omitted, it defaults to 1 (Saturday and Sunday).

Step 1: Basic Working Day Calculation with NETWORKDAYS

To calculate the net working days between a project start date in cell A2 and completion date in cell B2:

=NETWORKDAYS(A2, B2)

If A2 is 2026-03-02 (a Monday) and B2 is 2026-03-13 (a Friday), the formula returns 10 working days, completely ignoring the intermediate Saturday and Sunday.

Step 2: Excluding Holidays from the Calculation

To prevent regional bank holidays or company shutdowns from inflating working day counts, reference a dedicated holiday list:

1. Create a separate column (for example, cells F2:F10) listing all corporate and statutory holiday dates formatted as valid Google Sheets dates.
2. Incorporate the range into your formula:

   =NETWORKDAYS(A2, B2, $F$2:$F$10)

Any holiday that falls on a Monday through Friday will be subtracted from the total. If a holiday date happens to land on a weekend, Google Sheets intelligently avoids double-counting the deduction.

Step 3: Handling Custom Weekends with NETWORKDAYS.INTL

When working with shift patterns, retail schedules, or countries where the weekend falls on different days (such as Friday and Saturday in several Middle Eastern regions), standard NETWORKDAYS will yield incorrect totals. NETWORKDAYS.INTL provides two flexible methods to define weekends:

Method A: Using Predefined Weekend Numeric Codes

You can supply an integer code for the third parameter:

• 1: Saturday and Sunday (Default)
• 2: Sunday and Monday
• 7: Friday and Saturday
• 11: Sunday only (Six-day working week)
• 12: Monday only
• 17: Saturday only

Example: For a business operating in Dubai or Cairo observing Friday and Saturday as weekends:

=NETWORKDAYS.INTL(A2, B2, 7, $F$2:$F$10)

Method B: Using a 7-Character Binary String (Ultimate Customisation)

For non-standard or alternating work patterns, pass a 7-character string enclosed in quotes. Each character represents a day of the week, starting from Monday to Sunday, where 1 represents a non-working day (weekend) and 0 represents a working day:

"0000000" = Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

• Four-Day Work Week (Monday to Thursday on, Friday-Sunday off):

  =NETWORKDAYS.INTL(A2, B2, "0000111", $F$2:$F$10)

• Weekend Shift (Working only Saturday and Sunday):

  =NETWORKDAYS.INTL(A2, B2, "1111100", $F$2:$F$10)

• Wednesday and Sunday Off:

  =NETWORKDAYS.INTL(A2, B2, "0010001", $F$2:$F$10)

Step 4: Combining with ARRAYFORMULA for Entire Columns

Rather than dragging down formulas across thousands of client order rows, wrap the calculation in ARRAYFORMULA with blank-row protection:

=ARRAYFORMULA(IF((A2:A="") + (B2:B=""), "", NETWORKDAYS.INTL(A2:A, B2:B, 1, $F$2:$F$10)))

This dynamic array formula automatically computes working business days for every populated row in columns A and B while keeping empty rows completely clean.

Troubleshooting Common Errors

• #VALUE! Error: Occurs when start_date, end_date, or any holiday cells contain unparsed plain text instead of serial date values. Select the columns and click Format > Number > Date to ensure valid formatting.
• Negative Result: If end_date occurs before start_date, the formula returns a negative number representing the inverted day count. Use =ABS(NETWORKDAYS(A2, B2)) or verify chronological date entry.
• Binary String Syntax Errors: Ensure the binary string contains exactly seven digits containing only 0 and 1. Supplying six or eight characters will produce a #NUM! calculation error.