How to Calculate Business Days in Google Sheets (Exclude Weekends & Holidays)

Published on | ProSheetHub

Calculating the exact number of business days between two dates is essential for tracking project deadlines, managing client SLAs, and processing payroll. Standard date subtraction (End Date - Start Date) includes weekends and holidays, which skews your turnaround time metrics.

Google Sheets provides a built-in solution: the NETWORKDAYS function. This guide covers how to use NETWORKDAYS to calculate working days, exclude holidays, and handle custom weekend schedules.

What is the NETWORKDAYS Function?

The NETWORKDAYS function calculates the net number of working days between a start date and an end date. By default, it automatically excludes weekends (Saturday and Sunday).

Formula Syntax

=NETWORKDAYS(start_date, end_date, [holidays])
Parameter Requirement Description
start_date Required The starting date of the period.
end_date Required The ending date of the period.
holidays Optional A range or array of dates to exclude from the count (e.g., company or public holidays).

Step 1: Calculate Basic Working Days (Excluding Weekends)

If you only need to exclude Saturdays and Sundays, pass your start and end dates into the formula.

Example:

  • Start Date (Cell A2): 2026-09-01 (Tuesday)
  • End Date (Cell B2): 2026-09-15 (Tuesday)
=NETWORKDAYS(A2, B2)

Result: 11 working days.

Note: NETWORKDAYS includes both the start date and end date in its calculation if they fall on working days.

Step 2: Exclude Company & Public Holidays

To account for official holidays, create a holiday list in your spreadsheet and reference that range in the third argument.

Setup:

  1. List your holiday dates in a separate column (e.g., E2:E5).
  2. Enter the updated formula:
=NETWORKDAYS(A2, B2, E2:E5)

Sample Data & Calculation:

Start Date (A2) End Date (B2) Holidays Range (E2:E3) Result
2026-09-01 2026-09-15 2026-09-07 (Labor Day) 10

Google Sheets checks if any date in E2:E3 falls between A2 and B2 on a weekday and subtracts it from the total.

Step 3: Handle Custom Workweeks with NETWORKDAYS.INTL

If your business operates on non-standard weekends (e.g., Sunday-only weekends or Friday-Saturday weekends), use NETWORKDAYS.INTL instead.

Formula Syntax:

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

The weekend argument accepts a code or string defining which days are non-working:

Weekend Code Non-Working Days
1 (or omitted) Saturday, Sunday
2 Sunday, Monday
7 Friday, Saturday
11 Sunday only
"0000011" String format (1 = off, 0 = workday, starting from Monday). This example sets Sat & Sun as off.

Example (Sunday-Only Weekend):

=NETWORKDAYS.INTL(A2, B2, 11, E2:E5)

Common Pitfalls & Troubleshooting

  1. Dates Formatted as Text (#VALUE! Error):
    Ensure your date cells are recognized as actual dates by Google Sheets (Format > Number > Date). If dates are stored as text, wrap them with DATEVALUE() or re-enter them.
  2. Negative Day Count:
    If end_date is earlier than start_date, NETWORKDAYS returns a negative number. To avoid negative numbers in turnaround reports, wrap the formula with ABS() or an IF() statement: 
    =IF(B2>=A2, NETWORKDAYS(A2, B2, E2:E5), 0)
  3. Including End Dates in SLA Calculations:
    Remember that NETWORKDAYS counts both boundary days. If a task starts on Monday morning and finishes Monday afternoon, NETWORKDAYS(Mon, Mon) returns 1.

Comments

Post a Comment