Count Workdays and Set Due Dates in Google Sheets with Holidays
How do I add business days to a date in Google Sheets and skip weekends and holidays?
Use WORKDAY.INTL(start, workdays, "0000011", holiday_range). The seven-digit string makes Saturday and Sunday the weekend, and every date in the holiday range gets skipped. To go the other way, NETWORKDAYS.INTL counts the workdays between two dates and accepts the same weekend and holiday arguments.
| Row | B | C | D | Remove |
|---|---|---|---|---|
| 1 | Start | Workdays | Due | |
| 2 | Tue 2026-12-01 | |||
| 3 | Tue 2027-01-05 |
- Due date for the first task, skipping weekends and holidays
- Tue 2026-12-01
- Start plus calendar days
- Wed 2026-11-25
To add business days to a date in Google Sheets, use WORKDAY.INTL(start, workdays, "0000011", Holidays!A2:A20). It gives back the due date, skipping every day the seven-digit string marks as weekend and every date in the holiday range. NETWORKDAYS.INTL runs the opposite way: hand it two dates and it tells you how many workdays sit between them.
Plain WORKDAY and NETWORKDAYS will also do the job, but they only know one weekend, Saturday and Sunday. I would go straight to the .INTL versions. Then the day someone joins from a country with a different working week, the sheet needs no rewrite. If you would rather not type the formulas by hand, the workday formula builder produces the same ones with your cell addresses filled in.
How do you add workdays to a date in Google Sheets?
WORKDAY.INTL adds workdays to a date: give it a start date, a number of workdays, a weekend pattern and, if you want, a holiday list, and it returns the date that many workdays later.
The syntax in Google’s WORKDAY.INTL reference is WORKDAY.INTL(start_date, num_days, [weekend], [holidays]). The start date itself is never counted. Day one is the first workday after it.
Set up a Tasks sheet with the made-up rows below and leave columns D to F blank for now:
| A: Task | B: Start | C: Workdays | D: Due | E: Count | F: Reminder |
|---|---|---|---|---|---|
| Draft Q1 budget | 2026-11-20 | 5 | |||
| Vendor contract review | 2026-12-18 | 10 | |||
| Office move checklist | 3 | ||||
| Onboarding pack | 2026-11-21 | 1 |
Put this in D2 and fill it down:
=IF(OR(B2="",C2=""),"",WORKDAY.INTL(B2,C2,"0000011",Holidays!A2:A20))
Format column D as a date. Skip that and you get serial numbers. The outer IF leaves row 4 empty until someone enters a start date, which seems like tidiness for its own sake right up until other formulas start reading column D.
How do you exclude holidays from a due date?
You exclude holidays by listing each one on its own row of a separate sheet and passing that range as the fourth argument, so the formula skips those dates exactly as it skips weekend days.
Create a sheet named Holidays, put a header in A1 and add these dates below it:
| A: Date | B: Holiday |
|---|---|
| 2026-11-26 | Thanksgiving |
| 2026-11-27 | Day after Thanksgiving |
| 2026-12-25 | Christmas Day |
| 2027-01-01 | New Year’s Day |
Row 2 is the reason the list exists. The task starts Friday, November 20, and needs five workdays. Monday 23, Tuesday 24 and Wednesday 25 cover days one through three. Thursday 26 and Friday 27 are holidays, then comes the weekend, so day four is Monday 30 and D2 shows Tuesday, December 1. Take the holiday range out and the same formula returns Friday, November 27. Just add five calendar days to the start and you land on Wednesday, November 25. Three different deadlines for one row, and which one you get depends on the shortcut whoever built the sheet happened to take.
Row 3 runs into two holidays. Starting Friday, December 18, the ten workdays go through Thursday, December 24, jump over Christmas and the weekend, carry on to Thursday, December 31, jump over New Year’s Day, and finish on Tuesday, January 5, 2027.
According to Google’s reference, the holiday range should hold standard date values or date serial numbers. The trouble is that a holiday entered as text looks exactly like a real date in the cell. I would add =ISNUMBER(A2) in column C of the Holidays sheet and fill it down; any FALSE marks a date the formulas can’t read. Stopping the range at row 20 is deliberate. It leaves space for next year’s dates without aiming the formula at a full column of empty cells.
How do you count working days between two dates?
NETWORKDAYS.INTL(start, end, weekend, holidays) counts the workdays from the start date through the end date, and both of those dates count if they are workdays.
This is where reports drift a day. WORKDAY.INTL excludes its start date from the count; NETWORKDAYS.INTL includes it. Put this in E2 and fill it down:
=IF(OR(B2="",D2=""),"",NETWORKDAYS.INTL(B2,D2,"0000011",Holidays!A2:A20))
| Row | Start | Workdays added | Due | Count |
|---|---|---|---|---|
| 2 | Fri 2026-11-20 | 5 | Tue 2026-12-01 | 6 |
| 3 | Fri 2026-12-18 | 10 | Tue 2027-01-05 | 11 |
| 5 | Sat 2026-11-21 | 1 | Mon 2026-11-23 | 1 |
Rows 2 and 3 show a count one higher than the days added, because both tasks begin on a Friday and Friday is a workday. Row 5 begins on a Saturday, which NETWORKDAYS.INTL ignores, so the two numbers agree. Before a dashboard shows “workdays left”, settle whether today counts, and subtract 1 if it doesn’t. I would keep column E as a check while the sheet is being built and delete it once the dates look right. Count from the inputs rather than averaging figures derived from them; that rule applies just as much when you calculate a dashboard completion rate.
How do you set a weekend other than Saturday and Sunday?
You set a different weekend by changing the seven-digit string: each digit is one day, Monday first, and a 1 makes that day part of the weekend.
Both forms are defined in the NETWORKDAYS.INTL reference. "0000011" is Saturday and Sunday; "0000110" is Friday and Saturday. Common patterns can also be passed as a single number. Numbers 1 to 7 handle consecutive two-day weekends, from 1 for Saturday and Sunday through 7 for Friday and Saturday. Numbers 11 to 17 handle one-day weekends, beginning with 11 for Sunday only. I would use the string. It reads without a lookup table, and it covers patterns the numbers can’t, like a split Wednesday and Sunday weekend.
With a Friday and Saturday weekend, one workday after Thursday, November 19, is Sunday, November 22:
=WORKDAY.INTL(DATE(2026,11,19),1,"0000110")
Give it the default "0000011" instead and the same inputs return Friday, November 20. When one sheet serves teams with different weekends, put each row’s pattern in its own column and have the formula reference that cell rather than a fixed string. Format the column as plain text before typing into it. Otherwise Sheets may store 0000011 as the number 11, which is the code for a Sunday-only weekend, and the formula will quietly count Saturdays as workdays with nothing on screen to tell you.
How do you count back from a deadline?
You count back from a deadline by giving WORKDAY.INTL a negative number of workdays; it walks backwards from the date you supply and skips the same weekends and holidays on the way.
Google’s WORKDAY.INTL reference confirms that a negative num_days goes backwards. For a reminder three workdays ahead of each due date, put this in F2:
=IF(D2="","",WORKDAY.INTL(D2,-3,"0000011",Holidays!A2:A20))
Row 3 is due Tuesday, January 5, 2027. Going back, Monday, January 4 is one, New Year’s Day is skipped, Thursday, December 31 is two and Wednesday, December 30 is three. I like having the reminder date in a column of its own. It is much easier to check there than buried in a script, and it is exactly what a script should read if you send one daily task reminder from Google Sheets. If the due dates also go to a shared calendar, the script in Sync Google Sheets task deadlines to a Google Calendar expects its own four-column layout, so fill its Due Date column from column D here.
What makes a workday formula return the wrong date?
A workday formula usually returns the wrong date for one of three reasons: a date was typed inside the formula as text, a holiday cell contains text, or a weekend string lost its leading zeros.
| Symptom | Likely cause | Fix |
|---|---|---|
| Result near December 30, 1899 | Date typed into the formula as 11/20/2026 |
Use DATE(2026,11,20) or a cell reference |
| A holiday is still counted | Holiday cell holds text, not a date | Check with =ISNUMBER(A2) and retype it |
| Saturdays counted as workdays | Weekend cell stored 0000011 as the number 11 |
Format the column as plain text and retype |
| Error instead of a date | Weekend string is 1111111 |
Leave at least one workday in the pattern |
| Due date one day off in a report | Mixing the two functions’ counting rules | NETWORKDAYS.INTL counts the start date, WORKDAY.INTL doesn’t |
The first row is taken directly from Google’s notes on these functions. Per the WORKDAY reference, Sheets reads WORKDAY(10/10/2000,4) as 10 divided by 10 divided by 2000. The result is a tiny number, which Sheets then treats as a date near the beginning of its calendar. The NETWORKDAYS reference gives the same warning. Typing a date into a cell works because the cell converts it. Inside a formula, those same characters are just division.
The WORKDAY reference also says WORKDAY drops the decimal part of a fractional day count, so 1.9 workdays becomes one. If column C is built from hours or estimates, round it yourself beforehand so the sheet displays the number the formula actually used. For more ways to keep report numbers honest, see Spreadsheets and Data.