Workday Formula

[kumar1803]

Hello,

I need help with workday formula to calculate the number of days excluding weekend and holidays. Here is the data for dates:

Start Date End Date
12/14/2015 12/14/2015
12/15/2015 12/15/2015
12/12/2015 12/12/2015
12/10/2015 12/11/2015
12/8/2015 12/25/2015
12/10/2015 1/15/2016
12/25/2015 1/25/2016



Holiday
2016 1/1/2016
1/18/2016
5/30/2016
7/4/2016
9/5/2016
11/24/2016
11/25/2016
12/25/2016

 

[cerfani]

try this...

 

[kumar1803]

Hello,

The formula appears to be incorrect. I would like the same day if there is no increment for example: 12-14-15, 12-14-15. your formula counting as 1 in this case.

Also I don't the formula you have provided subtracting weekend or holidays. I am assuming Saturday and Sunday are off days

 

[cerfani]

the function network days does not count weekend or the dates listed in the holiday array. That is why you see 0 for 12/12/15 to 12/12/15 ... because this was a Saturday. If you have 12/14/15 to 12/14/15 showing as 0 then you won't be able to distinguish it from a range that is only on the weekend. But if you want to do that then just check if they equal and put 0 if they equal or just subtract 1 from the networkdays result.

=NETWORKDAYS(A2,B2,$A$11:$A$18)-1

or

=IF(A2=B2,0,NETWORKDAYS(A2,B2,$A$11:$A$18))