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.
- There are so many different ways to achieve the same goal;
- 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!
i am getting same result in both formula
a) NETWORKDAYS.INTL(F13,F16,11,0)
b)=NETWORKDAYS.INTL(F13,F16,11,5) i
Basically i want 5 holiday to exclude i.e from 22,23,24,25,26
LikeLike
Hi Arun,
If you want to exclude a list of holidays, you should input the list of holidays into a range of cell. E.g. input 22/3/2016, 23/3/2016, etc…. into say G1:G5 if you have five holidays as you said.
Then you formula should become
=NETWORKDAYS.INTL(F13,F16,11,$G$1:$G$5)
Hope this helps.
LikeLike
Hi, MF,
1) Solution 2+3 will not work in versions before “Excel 2010″.
2) For those old versions the first formula could be simplified – like:
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B1&”:”&B2)))=1))
3) After realizing that you (and others) like short & “sophisticated” formulas – try this:
=INT((B2-MOD(B2-1,7)-B1+7)/7) (for counting Sundays)
=INT((B2-MOD(B2-4,7)-B1+7)/7) (for counting Wednesdays)
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL
LikeLiked by 1 person
Hi Michael,
Thank you very much for your comments and your alternative solutions for users of Excel 2007 or before.
Your solution is elegant! Like the tricks of using MOD and +7 to make it work. Brilliant!
Thanks for enlightening! I’ve learnt something new today! 🙂
LikeLike
Hi, MF,
A slight correction.
It is not intended only for users of Excel 2007 or before.
It can be used in any version (97-2016) and it is the shortest formula (so far).
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL
LikeLike
Hi Michael,
Yes. You are right! Your solution works for all versions of Excel.
But for Excel 2010 or later, I would prefer the NETWORKDAYS.INTL function for its simplicity, provided that users need not to share the workbook to users of Excel 2007 or before … This is an uncertainty though🤔🤔
Cheers,
LikeLike