Calculate using NETWORKDAYS in Google Sheets

In a previous post I discussed how to calculate a future date based on working days, using the WORKDAY (or WORKDAY.INTL) Google Sheets function.

Sometimes, what you need to work out is the number of net working days between two given dates – for instance, to calculate how many working days will be lost while a piece of equipment isn’t available, or how many days’ cover you’ll need to hire for a colleague’s absence.

Google Sheets offers the NETWORKDAYS function (to save any confused looks, that’s net workdays, not network days!). Here’s the syntax:

NETWORKDAYS(start_date, end_date, [holidays])

Compare this with the syntax for WORKDAYS from the previous post and you’ll see that they’re very similar. With NETWORKDAYS, you provide the start date and end date of the period in question, plus (optionally) a range or array containing any non-weekend holidays, and the function will return for you the number of working days in the period:

Click image to enlarge

Click image to enlarge

The above example excludes the optional holidays parameter. Here’s the same example with some dates defined as holidays, and therefore excluded from the count:

Click image to enlarge

Click image to enlarge

If you work in a society that considers a weekend to be something other than Saturday and Sunday, there’s the function NETWORKDAYS.INTL, which lets you specify which days are weekend days:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Here, the optional weekend parameter is a number or string representing which days of the week are considered weekends. The weekend parameter can be specified in one of two ways:

The String method, where weekends can be specified using seven 0 and 1 characters; the first number in the string represents Monday and the last number, the following Sunday. A zero represents a work day, a 1 that the day is a weekend. For example, “0000111” would indicate that Friday, Saturday and Sunday are weekends.
The Number method, where a single number can be used as follows: 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and so on until 7 = Friday/Saturday. Alternatively, 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and so on until 17 = Saturday is the only weekend.

Leave a Reply

Your email address will not be published. Required fields are marked *