Count the Number of Selected Days of the Week in Google Sheets

Count the number of days of the week in a range Google Sheets

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!

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:

Google sheets End Date minus start date

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(B2A2) + 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

Google sheets NETWORKDAYS

We can see in our calendar below that both the 9th and 10th of May rest on a weekend (Saturday and Sunday).

May Calendar 2020 Windows 10

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:

Google sheets NETWORKDAYS example day of week countV2

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

Google sheets NETWORKDAYS minus holidays

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:

Google Sheets 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:

Google Sheets holidays inline NETWORKDAYS

=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

Google sheets total days minus NETWORKDAYS

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)

Google Sheets NETWORKDAYS INTL weekend example

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”)

Google Sheets NETWORKDAYS INTL weekend example string

Take a look at the key below for all the day pair keys:

Google Sheets NETWORKDAYS INTL two-day values of weekends

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:

Google Sheets NETWORKDAYS INTL single-day values of weekends

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 & B2example. 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

Google Sheets NETWORKDAYS INTL count of all Sundays

Check out some more examples:

Google Sheets NETWORKDAYS INTL selected days of the week

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:

Count the number of days of the week

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.

Want a solid step-by-step course to become a pro at Google Sheets? Udemy has some professional courses that will turn you into an admin ninja!

I’m a huge fan of Justin Mares, Mastering Google Sheets course. Sign up today*

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

~Yagi

Leave a Reply