Calculate Working Days Between Two Dates in Excel (excluding Weekend & Holidays)


Hello and welcome to this Excel tips video.
I am Sumit Bansal and in this video I’m going to show you how to calculate the number of
working days between two dates. So here is the first example where all I need to do is
calculate the number of days between these two dates and this is fairly straightforward.
I simply subtract the start date from the end date. So I select End Date minus Start
Date and it gives me the result. 73 is the total number of days between these two dates
excluding the end date. So if all of the days between these two dates are working days for
you, then this is a really straightforward formula. But in reality, this is rarely the
case. You would have weekend days, you would have holidays. So here I have another example where I want
to calculate the number of working days between these two dates and I do not want to count
holidays or weekend, which is Saturdays and Sundays. So I can use the NETWORKDAYS function
here where I specify the start date, the end date, and the holidays. So I would select
this range, press F4 to lock it and this gives me 50 as the total number of working days,
and these would not include holidays or Saturdays and Sundays . And you can do this for all
these cells. Now this is again a really useful formula, but sometimes you may have different
weekend days. Maybe you have a six-day work week where you only get the Sunday off or
maybe you have a weekend, which is not Saturday and Sunday, but maybe some other days, let’s
say Friday and Saturday. So in that case you can use a different formula,
which is the NETWORKDAYS.INTL Formula. Again, it takes the start date, the end date, and
it asks you to specify the weekend days. So you can choose from this combination. You
have two-day weekend days and you have single day weekend day. So you can select a Sunday
only, which means from Monday to Saturday will be working days and Sunday would be a
weekend day. So you can select that value, whatever you want to and then you select holidays,
press F4 to lock it, hit the Enter key and it gives you the total number of working days
with the selected weekend days that you have specified in the formula. Now let’s modify this formula a little bit
more to another scenario where you want to calculate the number of working days in a
part time job. Let’s say you’re working only on Tuesdays and Thursdays and you want to
calculate how many Tuesdays and Thursdays, the working Tuesdays and Thursdays are there
between these two dates. Again, we would use the NETWORK.INTL formula. Here we would have
the start date, the end date, and in the weekend part you can manually specify the working
days and non-working days. So are working would be specified with a 0 and a non-working
day with 1. So Monday is going to be non-working, then it will be Tuesday, (1) Wednesday (1),
Thursday (0), Friday (1), Saturday (1), and Sunday (1). So with this number in double quotes, you
have specified that only Tuesdays and Thursdays are working and then you can give a list of
holidays, press F4 to lock it and hit enter and it will give you the number of working
days, which only counts the working Tuesdays and Thursdays. You can also use the same formula
to count the total number of Mondays between these two dates, or total number of Fridays
between these two dates. Again, I will use the network days international formula. This
is the start date. This is the end date. Here, since I want to count the total number of
Mondays, I would keep 0 and then six times 1 because only Mondays are working. Then I
would have the holidays. I would press F4 and hit enter. If you do not want to have
holidays accounted for in the formula, you can leave this out. Also, if a Monday has a holiday, then that
would not be counted. So now, it instantly gives you that there are 8 Mondays between
these two date ranges. So this is how you can calculate the total number of working
days between two dates using these different formulas. That’s it in this video. I hope
you found this useful. Also, if you’re liking these videos, please subscribe to this YouTube
channel and click on the bell icon so that you’re notified whenever I come up with a
new Excel tips video. Thank you and have a nice day.

4 Comments

Add a Comment

Your email address will not be published. Required fields are marked *