NETWORKDAYS with Holidays in Google Sheets: Exclude Holidays from Working Days

To count working days between two dates in Google Sheets and skip holidays, list the holiday dates in a range and pass it as the third argument of NETWORKDAYS: =NETWORKDAYS(A2, B2, Holidays!A2:A). Weekends (Saturday and Sunday) are skipped automatically, and holidays that fall on a weekend are not subtracted twice.

Tested in Google Sheets. On November 9, 2026 we entered the formulas on this page in Google Sheets, including holidays on weekends, duplicate holidays and holidays stored as text, and compared the results with values calculated independently. How we test

Step 1: Make a holiday list

Create a sheet called Holidays and type one date per row in column A, starting in A2. For example, a company might use this list for 2026:

A: Date B: Name (optional) Day of the week
1/1/2026 New Year’s Day Thursday
4/3/2026 Company holiday Friday
5/25/2026 Company holiday Monday
12/25/2026 Christmas Day Friday
12/26/2026 Company holiday Saturday

Make sure the cells contain real dates, not text: they should be right-aligned by default, and changing the format to Format → Number → Number should show a serial number. The holiday names in column B are only for you; NETWORKDAYS ignores them because you only pass column A.

Step 2: Use NETWORKDAYS with the holiday range

With the start date in A2 and the end date in B2 of your main sheet:

=NETWORKDAYS(A2, B2, Holidays!A2:A)

The open-ended range A2:A means you can add next year’s holidays at the bottom of the list without editing the formula. Empty cells in the range are ignored.

Period Without holidays With the list above
Whole year 2026 (1/1 to 12/31) 261 257
Second quarter (4/1 to 6/30) 65 63
December (12/1 to 12/31) 23 22

For the whole year, the list has five dates but only four days are subtracted: December 26, 2026 is a Saturday, which is already a weekend day.

Optional: give the list a name

Select the dates and choose Data → Named ranges to call them Holidays. Then the formula becomes easier to read: =NETWORKDAYS(A2, B2, Holidays). A named range does not grow by itself, so include some empty rows below the last holiday or update the range when you add dates.

Type a few holidays directly into the formula

For a one-off calculation, you can write the holidays as an array of DATE values:

=NETWORKDAYS(A2, B2, {DATE(2026,12,25), DATE(2026,12,26)})

Do not type dates like 12/25/2026 inside the braces: in a formula, Google Sheets reads that as a division, not as a date.

Different weekend days: NETWORKDAYS.INTL

NETWORKDAYS always uses Saturday and Sunday as the weekend. If your weekend is different, use NETWORKDAYS.INTL, which takes the weekend as the third argument and the holidays as the fourth:

=NETWORKDAYS.INTL(A2, B2, 7, Holidays!A2:A)

Here 7 means a Friday and Saturday weekend. For December 2026 with the holiday list above, the result is 23: both December holidays fall on a Friday or a Saturday, so they are already non-working days.

Weekend code Non-working days
1 (default) Saturday and Sunday
2 Sunday and Monday
7 Friday and Saturday
11 Sunday only
17 Saturday only

You can also describe the weekend with seven characters, one per day from Monday to Sunday, where 1 marks a non-working day. "0000011" is the usual Saturday and Sunday weekend, and "0000110" is Friday and Saturday. Codes 3 to 6 and 12 to 16 cover the other combinations.

Find a date N working days later: WORKDAY

NETWORKDAYS counts the working days between two dates. To go the other way and find the date that is a number of working days after a start date, use WORKDAY with the same holiday list:

=WORKDAY(A2, 5, Holidays!A2:A)

For a start date of Monday, December 21, 2026, five working days later is Tuesday, December 29, 2026 with the holiday list, because December 25 is skipped. Without holidays it would be Monday, December 28. The start date itself is not counted. Format the result cell as a date (Format → Number → Date) if it shows a number.

Troubleshooting the holiday list

A holiday is not being subtracted

  • It falls on a weekend. That is expected: the day was already excluded.
  • It is outside the start and end dates. Holidays outside the period have no effect.
  • It was typed inside the formula as 12/25/2026. Google Sheets calculates that as a division, so the “holiday” matches no date and is silently ignored. Use DATE(2026,12,25) instead.

The same holiday appears twice

A date listed twice is only subtracted once, so duplicates do not break the count. Removing them still keeps the list easier to maintain.

Found in testing: a holiday stored as text is not skipped quietly. When we added a single text date such as “12/25/2026” to the holiday range, the whole NETWORKDAYS formula returned #VALUE!. A holiday typed inside the formula as {12/25/2026} did the opposite: it was silently ignored and the count did not change.

The result is #VALUE!

Either a date or a holiday is stored as text instead of a real date. Check the start and end dates first, then the holiday list: a single holiday entered as text, such as one pasted from another document, makes the whole formula return #VALUE!. Re-enter it as a date, or convert it with DATEVALUE. The holiday range should contain only dates and empty cells.

You need half days

NETWORKDAYS only counts whole days, so it cannot subtract half a day for a half-day holiday. Count the half days separately and subtract them, for example =NETWORKDAYS(A2, B2, Holidays!A2:A) - 0.5*C2, where C2 holds the number of half days.

Related guides