馃攳
Calculate Working Days Between Two Dates in Excel (excluding Weekend & Holidays) - YouTube
Channel: TrumpExcel
[0]
hello and welcome to this excel tips
[2]
video I am so mad pencil and in this
[5]
video I'm going to show you how to
[6]
calculate the number of working days
[8]
between two dates so here is the first
[11]
example where all I need to do is
[13]
calculate the number of days between
[15]
these two dates and this is fairly
[16]
straightforward
[17]
I simply subtract the started from ended
[20]
so I select this cell minus this cell
[22]
and it gives me the result and 73 is the
[25]
total number of days between these two
[27]
dates excluding the end day so if all of
[30]
the days between these two dates are
[31]
working for you then this is a really
[33]
straightforward formula but in reality
[35]
this is rarely the case you would have
[36]
weekend days you would have holidays so
[39]
here I have another example where I want
[41]
to calculate the number of working days
[43]
between these two dates and I do not
[45]
want to count holidays or weekend which
[48]
is Saturdays and Sundays so I can use
[50]
the network days function here where I
[53]
specify the start date the end date and
[56]
the holiday so I would select this range
[58]
press f4 to lock it and this gives me 50
[62]
as the total number of working days and
[64]
these would not include holidays or
[67]
Saturdays and Sundays and you can do
[68]
this for all these cells now this is
[71]
again a really useful formula but
[73]
sometimes you may have different weekend
[75]
days maybe you have a six-day Work Week
[77]
where you only get the Sunday off or
[80]
maybe you have a weekend which is not
[82]
Saturday and Sunday but maybe some other
[84]
days let's say Friday and Saturday so in
[86]
that case you can use a different
[88]
formula which is the network days
[90]
international formula again it takes the
[93]
start date the end date and it asks you
[97]
to specify the weekend day so you can
[99]
choose from this combination you have
[100]
two day weekend days and you have single
[103]
day weekend day so you can select Sunday
[105]
only which means from Monday to Saturday
[107]
would be working days and Sunday would
[109]
be a weekend day so you can select that
[112]
value whatever you want to and then you
[114]
select holidays press f4 to lock it hit
[117]
the enter key and it gives you the total
[119]
number of working days with the selected
[122]
weekend days that you have specified in
[123]
the formula now let's modify this
[126]
formula a little bit more to another
[128]
scenario where you want to calculate the
[130]
number of working days in a part-time
[132]
job
[133]
say you're working only on Tuesdays and
[135]
Thursdays and you want to calculate how
[136]
many Tuesdays and Thursdays the working
[138]
Tuesdays and Thursdays are there between
[140]
these two dates again we would use the
[142]
network international formula here we
[145]
would have the start date the end date
[148]
and in the weekend part you can manually
[152]
specify the working days and non-working
[154]
list so a working day would be specified
[156]
with a zero and a nonworking day with a
[159]
one so Monday is going to be non-working
[161]
then it would be Tuesday Wednesday
[163]
Thursday Friday Saturday Sunday so fit
[168]
this number in double quotes you have
[170]
specified that only Tuesdays and
[172]
Thursdays are working and then you can
[175]
give a list of holidays press f4 to lock
[178]
it and hit enter and if you give you the
[181]
number of working days which only counts
[184]
the working Tuesdays and Thursdays you
[186]
can also use the same formula to count
[188]
the total number of let's say total
[190]
number of Monday's between these two
[192]
dates or total number of fridays between
[193]
these two dates again I would use the
[195]
network days international formula this
[198]
is the start date this is the end date
[200]
and here since I want to count the total
[203]
number of Mondays I would keep 0 and
[206]
then 6 times 1 because only Mondays are
[210]
working and then I would have the
[212]
holidays I would press f4 and hit enter
[216]
if you do not want to have holidays
[218]
accounted for in the formula you can
[220]
leave this out also if a Monday has a
[224]
holiday then that would not be counted
[226]
so now it instantly gives you that there
[228]
are 8 Mondays between these two date
[231]
ranges so this is how you can calculate
[233]
the port number of working days between
[235]
two dates using these different formulas
[238]
that's it in this video I hope you found
[240]
this useful also if you're liking these
[243]
videos please subscribe to this YouTube
[244]
channel and click on the bell icon so
[246]
that you're notified whenever I come up
[248]
with a new excel tips video thank you
[251]
and have a nice day
Most Recent Videos:
You can go back to the homepage right here: Homepage