Google Sheets Business Days Formula

Quick answer

Google Sheets uses the same WORKDAY and NETWORKDAYS functions as Excel, with identical syntax. The key difference is that Google Sheets does not have a built-in UK bank holiday list — you must create your own holiday range in the sheet. Once that range is set up, =WORKDAY(A2, 20, E2:E20) gives you the date 20 working days after A2, excluding weekends and your listed holidays.

Core Google Sheets formulas

Worked example: FOI deadline in Google Sheets

A freedom of information request is received on Wednesday 3 September 2025. Under the FOI Act, the 20 working day clock starts the day after receipt. Here is how to calculate it in Google Sheets:

Step-by-step: setting up a Google Sheets deadline tracker

1. Create a new Google Sheet. Go to sheets.google.com and start a blank spreadsheet.
2. Enter your start dates in column A (e.g. the dates you received FOI requests or SARs).
3. Build a holiday list. In a separate column (e.g. E2:E10), enter UK bank holidays for the current and following year. Use the format DD/MM/YYYY. You can find these at GOV.UK bank holidays or copy from our 2026 bank holiday list.
4. Add the WORKDAY formula in column B: =WORKDAY(A2, 20, E$2:E$20). The dollar signs lock the holiday range so you can drag the formula down.
5. For FOI/SAR deadlines where the clock starts the day after receipt, use a two-step approach: column B = =WORKDAY(A2, 1, E$2:E$20) (day one), column C = =WORKDAY(B2, 19, E$2:E$20) (deadline).
6. Use conditional formatting to highlight approaching deadlines. Select column C, go to Format → Conditional formatting, and set a rule to highlight dates within the next 5 days.

Google Sheets vs Excel: key differences

• Date format: Google Sheets uses your locale settings. UK users should ensure dates are recognised as DD/MM/YYYY, not MM/DD/YYYY. If Sheets interprets your dates incorrectly, go to File → Settings → Locale and set it to United Kingdom.
• WORKDAY.INTL weekend codes: Both use the same codes (1 = Sat/Sun, 2 = Sun/Mon, 7 = Fri/Sat, etc.), but Google Sheets also accepts a seven-character string like “0000011” where each digit represents a day starting Monday (0 = workday, 1 = non-workday).
• No Analysis ToolPak needed: Unlike older versions of Excel, Google Sheets includes WORKDAY and NETWORKDAYS by default with no add-in required.
• Automatic saving and sharing: Google Sheets saves automatically and lets you share the tracker with colleagues in real time, which is useful for team deadline monitoring.

Using WORKDAY.INTL for non-standard weeks

If your organisation does not work a Monday–Friday pattern, use WORKDAY.INTL with a custom weekend code. For example, a Sunday–Thursday working week would use weekend code 7 (Friday/Saturday off): =WORKDAY.INTL(A2, 20, 7, E2:E20). Google Sheets accepts the same weekend codes as Excel, making cross-platform spreadsheets straightforward.

Using NETWORKDAYS for elapsed time tracking

To check how many working days have elapsed since a request was received, use =NETWORKDAYS(A2, TODAY(), E2:E20). This is useful for complaint and FOI deadline monitoring. Compare the result against the required number of working days to see if you are on track. Combine it with conditional formatting to highlight when you are within 3–5 working days of the deadline.

Google Sheets template workflow

1. Create a shared sheet with columns for Request Type, Date Received, Day One, Deadline, and Days Remaining.
2. Use =WORKDAY(B2, 1, HolidayRange) to calculate day one in the Day One column.
3. Use =WORKDAY(C2, DaysToAdd, HolidayRange) to calculate the deadline.
4. Use =NETWORKDAYS(TODAY(), D2, HolidayRange) to show working days remaining until the deadline.
5. Apply conditional formatting to the Days Remaining column: green for 10+ days, amber for 5–9, red for 0–4.
6. Share with your team using Google Sheets’ sharing settings so everyone sees the same deadline tracker.

Common mistakes in Google Sheets

• Date strings instead of dates. Typing “3/9/2025” may be treated as text. Use the DATE function or ensure the cell is formatted as a date.
• Empty holiday range. If your holiday column has blank cells within the range (e.g. E2:E20 but only E2:E8 are filled), WORKDAY may return an error. Keep the holiday range contiguous or use a filtered range.
• Not updating holidays annually. A sheet built in 2025 will give wrong results in 2026 if the holiday list is not refreshed.
• Confusing the start date. For FOI and SAR, the clock starts the day after receipt. Entering the receipt date directly into WORKDAY with 20 days gives you day 19, not day 20.

Key takeaways

• Google Sheets WORKDAY and NETWORKDAYS work identically to Excel for UK deadline calculations.
• You must create your own UK bank holiday list in the sheet — Google Sheets does not provide one.
• Set your locale to United Kingdom to avoid date format issues.
• Use WORKDAY.INTL if you need a non-standard weekend (e.g. some organisations work Sunday–Thursday).
• For FOI and SAR deadlines, use the two-step WORKDAY approach: find day one, then add the remaining working days.
• Verify your formula results against a dedicated deadline calculator for critical dates.

References

• Google Sheets — WORKDAY function help
• Google Sheets — NETWORKDAYS function help
• GOV.UK — UK bank holidays