Difference in Days Excluding Saturday and Public Holiday

**MarvinP** · 10-04-2010, 03:46 PM

Hey DL,

I think I can account for the extra Saturday using this formula.
If the weekday of the end date < weekday of the start date then it crossed two Saturdays
and we have to subtract an extra day. Formula of:

-IF(WEEKDAY(B2)<WEEKDAY(A2),1,0)

My entire formula is now:

=B2-A2-INT((B2-A2)/7)-COUNTIFS(Holidays,">"&A2,Holidays,"<"&B2)-IF(WEEKDAY(B2)<WEEKDAY(A2),1,0)

Can you patch it up and put your SUMPRODUCT into it and give it back to Kumara.
I need to study SumProducts awhile and whip myself for being wrong.

Thanks again for working this through with me. I really do appreciate it.

**MarvinP** · 10-04-2010, 04:50 PM

Hi Kumara,

Even though my formula was simpler, it was wrong. Use the daddylongleggs formula of:

=B2-A2+1-SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=7)+0)-SUMPRODUCT((Holidays>=A2)*(Holidays<=B2)*(WEEKDAY(Holidays)<>7))

Find the attached with both tested. Do more testing using both and understand why his is correctl

**Kumara_faith** · 10-04-2010, 11:17 PM

Hi daddylonglegs/Marvin,

My apologies for the inconvenience caused. Let me clarify based on your queries:

1) When the start date is Tuesday and the end date is two days later, a Thursday then you want that to count as 2 days.....are you actually counting the Tuesday and the Wednesday or the Wednesday and he Thursday to get that result of 2?

>>I am counting Wednesday and Thursday as 2 days

2) what should the result be if the start date was a Saturday and the end date was the next day.....or Start date is Friday and end date is Saturday?

>>If the start date is a Saturday and end date was the next day, then the count should be 1 day

>>If the Start date is Friday and end date is Saturday, then the count should be 1 day.

3) Also what about a situation where the start date is a Saturday and the end date is the following Saturday. Assuming no intervening holidays what result should that give?

>> The count should be 6 days.

Again, I really appreciate all your valuable time , effort and patience on this.

Please let me know if you need any other additional information and appreciate your expertise on this one.

**daddylonglegs** · 10-06-2010, 07:11 PM

Given those requirements the three equivalent formulas I posted above which count all the working days in the date range will only be wrong if both start date and end date are working days, so you could use this revised version

=B2-A2+1-INT((WEEKDAY(A2)+B2-A2)/7)-SUMPRODUCT((holidays>=A2)*(holidays<=B2)*(WEEKDAY(holidays)<>7))-(WEEKDAY(A2)<7)*(WEEKDAY(B2)<7)*(COUNTIF(holidays,A2)+COUNTIF(holidays,B2)=0)