在 Google Sheets 中計算工作日：排除節假日和自定義週末

Tested in: Live formula checks in Google Sheets on 12 November 2026. Results below come from those checks. I did not re-run the same workbook in Excel for this article; Excel has functions with the same names and argument order, but the counts here should be read as Sheets results.

Which versions this applies to

Google Sheets on the web and in the mobile apps. NETWORKDAYS and NETWORKDAYS.INTL also exist in current Excel; I did not verify the menu steps or the results in Excel this time.

Dates in the examples are real date values, not text that happens to look like a date. If a formula returns #VALUE! or a count that is clearly too low, check dates stored as text first.

A concrete booking

A contractor is booked from Tuesday 3 November 2026 through Friday 11 December 2026 and bills only weekdays. Put 2026-11-03 in A2 and 2026-12-11 in B2:

=NETWORKDAYS(A2, B2)

That returned 29. Thanksgiving in the United States falls on Thursday 26 November 2026 in this year. After adding that single date to a holiday list (the next step), the same range returned 28.

Those two numbers are the ones the rest of this tutorial keeps coming back to.

Step 1: Count Monday–Friday with NETWORKDAYS

NETWORKDAYS treats Saturday and Sunday as the weekend. It counts every other day in the range, including the start and the end when those days are weekdays.

A short week makes the inclusive rule obvious:

If you only needed a Monday–Friday count and had no holidays, you could stop here. Most invoices and project plans need at least one public holiday taken out.

Step 2: Keep holidays on their own sheet

Create a sheet named Holidays. Put a header in A1 and the dates starting in A2. One column is enough; a note in column B is optional.

For the checks in this article the list was:

Point NETWORKDAYS at that column. A2:A works, and so does a bounded range such as A2:A20. Extra blank cells at the bottom did not change the counts I got.

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

On the contractor booking (3 Nov to 11 Dec) this returned 28, because 26 November is inside the range and 25 December is not. Christmas is on the list for later use; it does not affect this particular booking.

A holiday that falls on a Saturday is not subtracted twice. The week from Monday 23 November to Sunday 29 November contains Thanksgiving (Thursday) and the Saturday test holiday:

• =NETWORKDAYS(A2, B2) returned 5
• =NETWORKDAYS(A2, B2, Holidays!A2:A) returned 4

Only the Thursday came off. The Saturday was already a weekend.

Two other checks from the same list:

• Monday 9 November to Friday 13 November, with Veterans Day on Wednesday the 11th, returned 4.
• A start and end that are both Thanksgiving returned 0. A start on Thanksgiving and an end on Friday 27 November returned 1 — only the Friday counts.

Step 3: Change the weekend with NETWORKDAYS.INTL

NETWORKDAYS will not help if the team works Sunday to Thursday, or if only Sunday is off. NETWORKDAYS.INTL adds a weekend argument before the holiday list:

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

Weekend argument 1 is Saturday and Sunday, the same rule as NETWORKDAYS. On the Thanksgiving week (Mon 23 Nov to Fri 27 Nov) both of these returned 4:

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

Weekend argument 7 is Friday and Saturday. For Sunday 8 November through Saturday 14 November, with no holidays, that returned 5 — Sunday through Thursday.

The same weekend can be written as a string of seven 0s and 1s that starts on Monday. A 1 marks a weekend day. Friday and Saturday are the fifth and sixth characters:

=NETWORKDAYS.INTL(A2, B2, "0000110")

On that same 8–14 November range, the string also returned 5. If you type 0000110 into a cell and Sheets stores it as the number 110, the formula returns #NUM!. Keep the value as text (type an apostrophe before the digits, or wrap it in quotes inside the formula).

I also checked weekend code 11 (Sunday only) on 8–14 November: the result was 6, Monday through Saturday.

I did not run every other weekend code. For a pattern I have not tested, the seven-character string is easier to inspect than a code number: a 1 in a position means that day is a weekend, starting at Monday.

Back to the contractor booking, Friday–Saturday weekend, no holidays:

=NETWORKDAYS.INTL(A2, B2, 7)

That returned 28. And Thanksgiving week with weekend 7 plus the holiday list returned 3: Monday, Tuesday and Wednesday. Friday is already a weekend in this system, and Thursday is the holiday.

Step 4: Find a date n working days later with WORKDAY

NETWORKDAYS counts the days between two dates. WORKDAY (and WORKDAY.INTL) starts at a date and moves forward a number of working days. The start date itself is not one of those days.

Three working days after Monday 23 November 2026, skipping Thanksgiving:

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

The result was Friday 27 November 2026 — Tuesday, Wednesday, then Friday.

WORKDAY.INTL takes the same weekend codes. Three working days after Sunday 8 November, with weekend 7 (Friday and Saturday off; Sunday is a workday), returned Wednesday 11 November. Sunday is the start, so it is not counted; the three days are Monday, Tuesday and Wednesday.

If the start falls on a weekend, WORKDAY skips to the next working day and then counts from there. One working day after Saturday 14 November 2026 returned Monday 16 November.

When the start is after the end

I ran =NETWORKDAYS(DATE(2026,11,13), DATE(2026,11,9)) — Friday back to the preceding Monday. Sheets returned -5, not an error. If you might paste dates in either order, wrap the call in a check rather than assuming a negative count will be noticed:

=IF(B2<A2, "Start is after end", NETWORKDAYS(A2, B2, Holidays!A2:A))

Common mistakes

Typing 0000110 as a weekend code and letting Sheets treat it as 110, which raises #NUM!. Force it to text, or use weekend code 7 instead.

Using NETWORKDAYS for a Friday–Saturday weekend. The count will look almost right and still be wrong; switch to NETWORKDAYS.INTL.

Assuming a holiday that lands on Saturday will take another day off the Monday–Friday count. It won’t.

Expecting WORKDAY’s start date to be included in the n days. It is the date you start from, not day one of the count.

Feeding the functions text that looks like a date. Fix the cells first; the dates stored as text tutorial covers the usual repairs.

FAQ

Does NETWORKDAYS count the start date and the end date?

Yes, when those dates are working days. Thursday 12 November 2026 to itself returned 1. The same formula on Saturday 14 November returned 0.

How do I count working days if Friday and Saturday are the weekend?

Use NETWORKDAYS.INTL with weekend argument 7, or with the text string "0000110". On 8–14 November 2026 both forms returned 5.

What happens if the start date is later than the end date?

In the Sheets check, NETWORKDAYS returned a negative number (−5 for 13 November back to 9 November). It did not throw an error.

How do I get the date n working days after a start date?

WORKDAY for a Saturday–Sunday weekend, WORKDAY.INTL for any other weekend. Three working days after 23 November 2026, excluding Thanksgiving, was 27 November 2026.

Can I use these formulas in Excel?

Excel provides NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY and WORKDAY.INTL with the same argument order. The figures in this article come from Google Sheets on 12 November 2026 and were not retested in Excel here.

Related tutorials

• Calculate age from a date of birth in Google Sheets
• Excel dates stored as text
• Sum time over 24 hours in Google Sheets
• Hours worked on overnight shifts, with a timesheet
• Hours worked calculator

Live-test findings

All counts in the tables and examples were entered in a private Google Sheet on 12 November 2026 and read back from the calculated cells. I did not test every NETWORKDAYS.INTL weekend code; the ones I ran were 1, 7, 11, and the string "0000110". Locale-specific date parsing and semicolon argument separators were not part of this check.

Sources

• Google Sheets function list for NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY and WORKDAY.INTL, used for argument order and the meaning of weekend codes 1, 7 and 11. The counts themselves come from the 12 November 2026 workbook, not from the documentation examples.