Google Sheets: DAYS, NETWORKDAYS.INTL, NETWORKDAYS, VALUE
A very common spreadsheet task is to get the total count of the days between two dates. We might need this information to:
- Find the number of days absent of staff or students.
- Find the total days worked or attending a class.
Some times we need to avoid including weekends, national holidays or certain days of the week in our total account. Fortunately for us, Google Sheets has some custom functions to help us easily work this all out.
This tutorial will cover how to find the total number of days over a range minus any selected days you don’t want to be included. We’ll also cover how to get the total count of specific days of the week over a range.
NOTE! I have the dates set to the British standard e.g. 5 May 2020. You can change the formatting around to any gregorian date format for these examples.
Let’s get cracking!
Table of Contents
Find the Total Count of Days Over a Range of Two Dates
To get the net total days between two dates your first instinct might be to simply subtract the end date from the start date.
For example, let’s say we want the total number of days between 5 May 2020 and 10 May 2020. The calculation is pretty easy to do in our head – it’s 5. We subtract the 10 from to 5 to get us, 5. We might be inclined to subtract the end month from the start in our Google Sheet like this:
You could even use Google Sheet’s DAYS function. DAYS takes two arguments:
- The end date: e.g. 10 May 2020
- The start date: e.g. 5 May 2020
So the same formula would look like this:
=DAYS(“End Date”, “Start Date”)
=DAYS(“10 May 2020”,“5 May 2020”) // = 5
=DAYS(B2,A2) // = 5
Why would you use this formula? Well, it does make what you are doing more explicit for someone else to read your work at a glance, I guess.
A slight problem: Count to include all days
For those of you playing at home who are counting on your fingers, counting from the 5 to the 10 May 2020, you might have noticed that the total count does not include the last day.
Most date ranges we are trying to determine will need to include the last day. To fix this, we simply add one to either one of our calculations.
=B2 – A2 + 1 // = 6
=DAYS(B2, A2) + 1 // = 6
Count the Days Minus Weekends: The Standard Weekend
Monday through to Friday is the standard workweek for many countries around the world. So what if we want to count all the days between and including two dates, but not include the weekends?
Here, we can use the Google Sheets NETWORKDAYS function. This function takes two arguments:
- The start date: e.g. 5 May 2020
- The end date: e.g. 10 May 2020
It returns the total net count of days minus any weekends that may appear.
It’s important to note that NETWORKDAYS includes both the start and end date in its calculation, unlike DAYS or subtracting the end date from the start date. This means there is no need to add one to the calculation.
=NETWORKDAYS(“Start Date”, “End Date”)
=NETWORKDAYS(“5 May 2020”,“10 May 2020”) // = 4
=NETWORKDAYS(A2, B2) // = 4
We can see in our calendar below that both the 9th and 10th of May rest on a weekend (Saturday and Sunday).
So for our example dates 5-10 May 2020, only 4 of the 6 days lie on a weekday. Take a look at a few more examples:
Don’t Include Holidays
NETWORKDAYS also has an optional function that can also exclude specific set dates. These are usually public holidays for many of us.
Holidays are captured in the NETWORKDAYS third argument as an array of date values.
=NETWORKDAYS(“Start Date”, “End Date”, array of date values)
The Common easy approach
The most common way of adding holiday values is to reference a range of cell values in Google Sheets.
Let’s say my date range is from 1 Mar 2020 to 30 Sep 2020 (A2 & B2). The total net number of days would be 214. If we took away the weekends, that would leave us with 153 days. In cells B7:B10 we have a list of public holidays that we need to take into account. Our formula would look like this:
=NETWORKDAYS(A2, B2, B7:B10) // = 150
As you can see, the result is 150. The third, holiday, argument in NETWORKDAYS removes the days from the range we selected. It does not subtract First Contact Day because that lies on a Sunday, our designated weekend.
The harder in-line approach
Fair warning: this little section digs into the weeds a bit, feel free to skip it.
So if you cannot possibly select a cell range for your holidays, you can put them inline inside your code by using Google Sheet’s array curly braces “{}”.
However, we can’t just simply slap a few dates inside the curly braces and call it a day. You may not know this, but there is some dark esoterica that goes on in the background of managing dates in Google Sheets.
Each date you enter into your spreadsheet is stored as a number value. So the 23 May 2020 is stored as: 43974 – All the way back to 30 Dec 1899 (00000).
When using the array curly braces we need to convert our date to this number value. We can do this by using the VALUE function:
Now, back to adding in our holidays. To add in our holidays in-line we would need to put each date inside our VALUE. Like this:
=NETWORKDAYS(A2,B2,{VALUE(“16-Mar-2020”),VALUE(“5-Apr-2020”),VALUE(“8-May-2020”),VALUE(“18-Sep-2020”)})
// = 150
Or you can tidy it up a little using ARRAYFORMULA:
=NETWORKDAYS(A2,B2,ARRAYFORMULA(VALUE({“16-Mar-2020”,“5-Apr-2020”,“8-May-2020”,“18-Sep-2020”})))
// = 150
Count Just the Weekends
What if we just wanted to count just the weekend days instead of the weekdays?
Here we can subtract the total count of all the days in the range from the NETWORKDAYS.
=DAYS(“End Date”, “Start Date”) + 1 -NETWORKDAYS(“Start Date”, “End Date”)
=DAYS(“10 May 2020”, “5 May 2020”) + 1 -NETWORKDAYS(“5 May 2020”, “10 May 2020”) // = 2
=DAYS(B2, A2) + 1 -NETWORKDAYS(A2, B2) // = 2
Count the Days Minus Weekend and other One, Two or Multi-Day Pairs
Not every country in the world has its weekend on Saturday and Sunday. Some people work different shifts too. So how do we take this into account in Google Sheets?
Introducing the NETWORKDAYS.INTL function.
NETWORKDAYS.INTL is a powerful beast, capable of managing a multitude of tasks.
It takes 4 arguments:
- Start Date, e.g. 5 May 2020
- End Date, e.g. 10 May 2020
- Day specifier, e.g. numerical (1, 2 ,3 etc) or seven-character string(“0011000”)
- Holidays (optional) e.g. a range or in-line array.
Minus two-day pairs
To get the total count of a range minus a specific two-day pair (weekend) NETWORKDAYS.INTL offers two approaches:
=NETWORKDAYS.INTL(“Start Date”, “End Date”, Number Code)
=NETWORKDAYS.INTL(“Start Date”, “End Date”, “7-character string”)
NETWORKDAYS.INTL has a code of 7 two day pairs. Saturday-Sunday is assigned 1; Sunday-Monday, 2 etc.
Let’s say we want to count the number of days between 5 May 2020 and 10 May 2020, but not include Friday and Saturday. The pair code for Friday and Saturday would be 7. For example:
=NETWORKDAYS.INTL(“5 May 2020“, “10 May 2020”, 7)
=NETWORKDAYS.INTL(A2, B2, 7)
Alternatively, we could represent the third argument, the weekend, assignment as a string. This string is a set of 7 characters, either “0” for weekday or “1” for the weekend. You could also consider “0” as, include this day in the count and “1” as excluding this day in the count.
The string always starts on Monday. So if we want to remove Friday and Saturday from out count we would make a string like this: “0000110”.
=NETWORKDAYS.INTL(“5 May 2020“, “10 May 2020”, “0000110”)
=NETWORKDAYS.INTL(A2, B2, “0000110”)
Take a look at the key below for all the day pair keys:
Minus single-day pairs
Working a 6-day week? That sucks.
If you need to remove a single day of the week from your calculations, then NETWORKDAYS.INTL has you covered too.
All single-day numerical codes start from 11 for Sunday and runs through to 17 for Saturday. This means that if we are only taking off, say, Friday each week our example would look like this:
=NETWORKDAYS.INTL(A2, B2, 16) // = 5
Likewise, for our string set, we would make all characters zero except for the fifth character, “0000100”:
=NETWORKDAYS.INTL(A2, B2, “0000100”) // = 5
Check out the single-day key below:
Multi-Day Exclusions
The power of using the string approach comes in when you want to exclude a group of days in a week.
Let’s say we want to exclude Tuesday and Saturday. we could do this easily with the string, “0100010”. What about excluding Monday, Wednesday and Thursday? Easy! “1011000”. All you need to do is remember that the first character of the string starts on Monday.
Only count selected days of the week in a range
Using the string approach to NETWORKDAYS.INTL we can easily count only those selected days.
Let’s go back to our 1 Mar 2020 to 30 Sep 2020 (A2 & B2) example. We know that the total number of days is 214. What if we want to know how many Sundays there are in this range. We can do this by inverting the Sunday string “0000001” to “1111110”. This will now only count Sundays.
=NETWORKDAYS.INTL(A2, B2, “1111110”) // = 31
Check out some more examples:
Of course, you can do this for more than one day and select what days you want to be seen by marking them with a zero.
Conclusion
You can check out my Google Sheet examples here:
Just go to File > Make a copy to play with it.
I’ve recently used this formula when getting the count of staff absences. I’ve then set up a dashboard to chart the popularity of days of the week against the frequency of absences.
What would you use this for? I would love to hear in the comments below.
If you liked this post and want to get regular updates, please subscribe (top-right). I post 2 to 4 times a month.
Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
~Yagi