NETWORKDAYS.INTL Function – Google Sheets

NETWORKDAYS.INTL calculates the number of working days between two dates. The function excludes weekends (which may or may not be Saturday and Sunday) and can optionally exclude holidays.

If your weekends are Saturday and Sunday, consider using the simpler NETWORKDAYS function.

💡 The start_date and end_date are included in the count of days.

Syntax

=NETWORKDAYS.INTL(start_date,end_date,[weekend day(s)],[holiday(s)])

start_date The date at which to start the counting

end_date The date at which to end the counting

[weekend day(s)] An optional description of which days are weekends.

  • Number method
    • 1 – Saturday and Sunday [don’t need to specify]
    • 2 – Sunday and Monday
    • 3 – Monday and Tuesday
    • 4 – Tuesday and Wednesday
    • 5 – Wednesday and Thursday
    • 6 – Thursday and Friday
    • 7 – Friday and Saturday
    • 11 – Sunday only
    • 12 – Monday only
    • 13 – Tuesday only
    • 14 – Wednesday only
    • 15 – Thursday only
    • 16 – Friday only
    • 17 – Saturday only
  • String method – Each digit represents a day. The first digit is Monday. Some examples are as follows:
    • 0000011 – Saturday and Sunday
    • 0100000 – Only Tuesday

[holiday(s)] An optional list of holidays to exclude from the count of days.

Video Tutorial

Related Functions

DAYS – Calculate the number of days between two dates.

DATEDIF – Calculate the time between two dates in years, months, or days.

MINUS – Subtract one value from another. The MINUS function can subtract dates.

NETWORKDAYS – Calculate the number of working days without the weekend modifications of NETWORKDAYS.INTL

Errors

#VALUE! – An input(s) isn’t a valid date such as “The other day” or “Yester-yester-day”.

Examples

Example 1 – One Day Weekends (1st Method)

In this first example, we specify that only Sundays are weekends.

Using NETWORKDAYS.INTL with an 11 for weekends
Using 11 for Weekends

Formula used: =NETWORKDAYS.INTL(A2,B2,C2)

We tell Google Sheets to only use Sundays as weekends by using an 11 for the weekend code. In this example’s formula, we do this by referencing cell C2 which contains the number 11.

A calendar with the days being counted underlined
22 Days

This calendar shows us which days are counted. Each full week has six workdays and after accounting for the two partial weeks, we come to 22 workdays.

💡 See this video to learn about the pop-up calendar in Google Sheets.

Example 2 – One Day Weekends (2nd Method)

There is a more intuitive technique for specifying weekends.

The NETWORKDAYS.INTL function with a string specifying the weekends
Using a 0000001 Weekend Day

Formula used: =NETWORKDAYS.INTL(A2,B2,C2)

With this technique, you can use a text string of seven digits like in cell C2. In this string, a 0 indicates a workday, and a 1 indicates weekends. Each digit’s location inside the seven-digit string indicates the day of the week, with the first digit being Monday. Notice the apostrophe before the seven numbers. This tells Sheets to treat the value as text instead of a number.

Using an apostrophe to make numbers a string
Making Numbers Into A String

Since both examples 1 and 2 use Sundays as weekends, they each result in 22 workdays.

Example 3 – Custom Weekends and a Holiday

Similar to the NETWORKDAYS function, NETWORKDAYS.INTL lets you specify holidays. The holidays argument is optional, so leave it off if you don’t have any in your calculation.

NETWORKDAYS with custom weekends and one holiday
Custom Weekends and One Holiday

Formula used: =NETWORKDAYS.INTL(A2,B2,C2,D2)

The holiday is placed in cell D2. As seen in the calendar, the Sunday weekends and the holiday of November 24th are excluded.

A calendar with the days being counted underlined with a holiday being excluded
21 Days

With the holiday subtracted, there are 21 workdays between November 4 and November 29, 2022.

Live Examples in Sheets

Go to this spreadsheet for examples of the NETWORKDAYS.INTL function shown above that you can study and use anywhere you would like.

Notes

Consider using the IMPORTXML function to obtain a list of your local holidays from the web.

6 thoughts on “NETWORKDAYS.INTL Function – Google Sheets”

  1. Hi, at my PC in windows 7, excel sheet NETWORKDAYS is giving incorrect answers. Actually i want to manage my workdays attendance and i tried it. I put 2-April-2020 in start date cell and 15-April-2020 in end date cell, the result should be 14 but it is giving 10-January-1900. I tried many times and took also google help but all in vain and time wasting. Your help would be very helpful for me. Thanks, Regards.

    Reply
    • Change the format from date to number. That sounds like a number that your spreadsheet is trying to show to you as a date.

      Reply
    • You can input it as a Custom function if you scroll all the way down through the preset options in the Conditional formatting menu.

      Reply
  2. is there a formula where I can specify the holidays, but does not omit weekend? Trying to calculate between two dates, include weekends, but exclude list of holidays.

    Reply

Leave a Comment