This post is about how to twist the NETWORKDAYS.INTL function to solve the question.

About two years ago, I wrote a post <Calculate number of a specific day between two dates> to explain how the following formula (Solution 1) could solve the question:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"DDDD")="Sunday"))

I like this formula very much for solving the question…until one day in the beginning of this year, Chandra Mohan left a comment to suggest an alternative formula (Solution 2):

=ED-SD-NETWORKDAYS.INTL(SD,ED,11)+1 
where ED is End Date; SD is Start Date

Wow!

While I was still impressed with the use of NETWORKDAYS.INTL function and the simple logic behind this solution, another comment by John Jairo V came in two days later, with an even better twist of using NETWORKDAYS.INTL for this question (Solution 3):

=NETWORKDAYS.INTL(A1,B1,"1111110")

Simply awesome!!!  (I will tell you how awesome it is by the end of this post.)

This is why I love Excel so much.

1. There are so many different ways to achieve the same goal;
2. When I think I know something, I am not indeed.  There is always new excitement!

So in this post, I am going to show you the less-known function NETWORKDAYS.INTL and how it helps to solve the problem. (Interestingly many people who know NETWORKDAYS are not aware of NETWORKDAYS.INTL, are you?).

Before we dive into the solutions 2 & 3, let’s take a look at NETWORKDAYS.

NETWORKDAYS returns the number of working days between two dates.

The syntax is quite straightforward

=NETWORKDAYS(Start_Date, End_Date, [Holidays])

Referencing to our example, (i.e. substituting 1/3/2016 into Start_Date; 1/4/2016 into End_Date; assuming there are no holidays).  the formula would becomes:

=NETWORKDAYS(B1,B2) where B1 is Start Date; B2 is End Date. Note: You may put a list of holidays in a range, e.g. D1:D10,
and then put that range as the third argument.
Excel will exclude holidays accordingly

which returns 24 as a result.

Note: Both dates are inclusive

Nevertheless, there is one major assumption (limitation): Every people on this planet rests on Saturday and Sunday!  This is absolutely not the case…

For sake of being “International”, Excel introduced NETWORKDAYS.INTL in Excel 2010.

There is one additional argument – [Weekend] for this “new” function so that we may  customize weekend parameters.

The syntax (is still straightforward)

=NETWORKDAYS.INTL(Start_date, End_date, [Weekend], [Holidays])

Say for example, we only take off on Sundays (which is not uncommon in Hong Kong), we need to input 11 for the [Weekend] argument, i.e.

=NETWORKDAYS.INTL(B1,B2,11)

would returns 28 as a result.

You may count it mannully if you don’t trust the formula.  :p

The following screenshot shows majority of the predefined options for specifying weekend.  You should see that too when you type along…

As seen above, we may select any single day in a week, or any two consecutive days in a week as [Weekend].  That should be good enough to cater most people on this planet.  However Excel takes one step further to make sure that the function is good enough to (almost) every one by allowing users to define their very special [weekend] through a binary text string, e.g. “1001000”.

This text string must be of 7 letters long; and accepts either “0” or “1”.  The first letter represents Monday; the second letters represents Tuesday……; the 7th letters represents Sunday.

Since we are talking about weekend, we could easily associate what the 0 and 1 mean, like normally what they mean in Excel.  0 = False; 1 = True.

In other words,

• “0”… sorry this is not a weekend (working day);
• “1”… yeah this is a weekend (non-working day).

So a text string “1001000” would tell Excel that Monday (the first letter being 1) and Thursday (the forth letter” being 1) are weekends.

By using this binary text string as [weekend] argument, we may basically custom any kinds of weekend except “1111111” and “0000000”…  Why?

Technically, the function accepts both strings as third argument.  But…

• For “1111111”, if all seven days in a week are weekend, what weekend actually means to you??  Excel returns 0 as a result for sure.
• For “0000000”, if there are no day off in a week… what’s the point of using the NETWORKDAYS.INTL function?  Do it by subtraction.

Make sense?

To “picturize” the text string, we may imagine a cross for a “1”; nothing for “0”.

So that a text string of “0000001” means crossing out Sundays, i.e. Sunday Off

=NETWORKDAYS.INTL(B1,B2,"0000001")
(Note: The "" is necessary.  
If you want to use cell reference, make sure you start with an apostrophe
comma ' before the number. i.e. '0000001 in the reference cell)

would returns the same result (i.e. 28) as

=NETWORKDAYS.INTL(B1,B2,11)

In words, there are 28 working days between 1/3/2016 and 1/4/2016 assuming Sundays off only, and no other holiday in the period.

Look at one more example:

=NETWORKDAYS.INTL(B1,B2,"1001000") 'returns a result of 23

 All Mondays and Thursdays have been excluded…

There are 23 working days in the period assuming Mondays and Thursdays off, and no other holidays.  (You may count it manually to verify)

This is it.  A simple function with great help to people who works with scheduling or project management.

Now let’s look at the question in the beginning:

How many “*days” in a given period? And how the solutions 2 & 3 work?

Suppose it is “Sunday” that we want to count.

Solution 2

=ED-SD-NETWORKDAYS.INTL(SD,ED,11)+1 
where ED is End Date(1/3/2016); SD is Start Date(1/4/2016)

• a) The portion ED-SD+1 is a simple way to calculate the number of days (#Days) in the period, offset by +1 to include both dates.
• b) The portion NETWORKDAYS.INTL(SD,ED,11) is to count how many days except Sundays (#Days – #Sundays) in the period.

Therefore the whole formula does the following:

a) – b) = #Days – (#Days – #Sunday) = #Sunday

WOW!  So simple. 🙂

Solution 3

=NETWORKDAYS.INTL(A1,B1,"1111110")
where A1 = Start Date (1/3/2016); B1 = End Date(1/4/2016); "1111110" = Mon-Sat off

Obviously by specifying Monday to Saturday as weekend, we instruct Excel to count Sundays in the period.  As simple as that! 🙂

The best part of this twist is the flexibility when dealing with such question:

How many *days in the given period?

Suppose we want to count Mondays, Wednesdays and Fridays, what we need to do is to adjust the [weekend] argument accordingly:

=NETWORKDAYS.INTL(A1,B1,"0101011")

(Still wanna count manually?)

Isn’t it awesome?

Thanks Chandra Mohan and John Jairo V again for your contribution!

By the way, the text string “1111111” works for me for the coming week as it is the Chinese New Year of Monkey.  I am going to take a week off.

Wish you and your family a fruitful, healthy and Excellent Year of Monkey!

See you in the year of Monkey!