Calculate number of “*day” in a given period

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

Excel Tips - Calculate Xday in a period.PNG

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:


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

where ED is End Date; SD is Start Date


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


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.

Excel Tips - Calculate Xday in a period 1.PNG

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.

Excel Tips - Calculate Xday in a period 1.1.PNGNote: 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.Excel Tips - Calculate Xday in a period 2.PNG

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.


would returns 28 as a result.

Excel Tips - Calculate Xday in a period 4.PNG

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…Excel Tips - Calculate Xday in a period 3.PNG

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

Excel Tips - Calculate Xday in a period 5.PNG

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

(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


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

Excel Tips - Calculate Xday in a period 6.PNG 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

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

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!🙂

Excel Tips - Calculate Xday in a period 7.PNG

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:


Excel Tips - Calculate Xday in a period 8.PNG(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!

This entry was posted in Formula and tagged , . Bookmark the permalink.

6 Responses to Calculate number of “*day” in a given period

  1. Arun says:

    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


    • MF says:

      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
      Hope this helps.


  2. Michael (Micky) Avidan says:

    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:

    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)

    Liked by 1 person

    • MF says:

      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!🙂


      • Michael (Micky) Avidan says:

        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)


        • MF says:

          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🤔🤔


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s