How to count days excluding weekends and holidays but start day/end day can be weekends /holidays?

0
votes

How to count days excluding weekends and holidays in excel. However, start day and end day can be weekends or holidays.

I have tried Networkdays function in excel using MAC. I also tried workdays function. And, i am new to this forum. Let me know if you are not able to understand anything.

Start Date(E) End Date (F) 2019-07-20 - 2019-08-01 2019-08-04 - 2019-08-06 2019-08-05 - 2019-08-07 2019-08-02 - 2019-08-07 2019-08-14 - 2019-08-20

Holiday list 

Date(K) Description   Day

8/12/19 - Eid - Monday
8/15/19 - Independence Day - Thursday

Excel formula:

= NETWORKDAYS(E2,F2,$K$7:$K$27)

Start date: Column E, End date: Column F, Holiday list: Column H

output expected output 9 8 2 1 3 1 4 2 4 2

1
excelmacosexcel-formula
Welcome to SO. Could you please clarify how would you calculate your expected output? I don't quite understand the logic behind these values. For example, first row (20/07/2019 - 01/08/2019) properly present the output of 9 as 20/21.07 are weekends, 22-25.07 are working days (Mon-Fri), 27-28.07 are weekends and then 29.07-1.08 are working days (Mon-Thu), giving us 9 in total. Your holidays list contains 2 holiday dates which do not fall on this period. It'd be easier to help once I understand your approach. - Justyna MK
Sorry for the confusion. I have made changes. So my question is that while calculating, it exclude Weekends and holidays. But start day or end date can be on weekends/ Holidays. if start day is 2019--08-04 and end day is 2019-08-06, it shows only difference. - Shweta Mehna
Thanks for explaining - in the case of 2019-08-04 - 2019-08-06, why the result is 1? 4th is Sunday (weekend), 5th is Monday (working day) and 6th is Tuesday (working day) - shouldn't the result be 2? - Justyna MK
What day of the week are you using as day 1? - Solar Mike
@JustynaMK thanks for your quick reply and your valuable efforts . Here we can use 2 approaches: 1) Including end date : If i include end date (2019-08-04 - 2019-08-06) then the result should be 2. 2) Excluding end date: If i do not include end date (2019-08-04 - 2019-08-06) then the result should be 1. ex- if i finish my work on 2019-08-04(Sunday) and i delivered the report for the same on 2019-08-12(Monday). So, here it should count only work day. Work days are Monday to Friday. Difference can be 5(excluded: 2019-08-12) or 6(included: 2019-08-12). - Shweta Mehna

1 Answers

0
votes

Try using NETWORKDAYS.INTL to make sure that your weekend days are set to fall on Saturdays and Sundays.

For example:

=NETWORKDAYS.INTL(E2+1,F2-1,1,$H$2:$H$3)

Result:

enter image description here