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. UseDATE(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.