Difference in Days Excluding Saturday and Public Holiday

**Kumara_faith** · 09-23-2010, 08:37 PM

Hi,

I have the following table as attached. I need a formula to calculate the difference in dates based on the following criteria:

a) Exclude Saturday
b) Exclude Public Holidays
c) If 6 Sept 2010 is the start date and 8 Sept 2010 is end date, then the difference should be 2 days instead of 3 days.

In the example as attached, the difference between 6 Sept 2010 and 13 Sept 2010 should be 4 days after excluding Saturdays and Public Holidays.

Appreciate help.

I have a cross post here:

http://www.mrexcel.com/forum/showthr...17#post2454117

**MarvinP** · 09-24-2010, 01:04 AM

Try the attached to see if it works for you.

You may need absolute cell instead of relative cell referencing when you get it into your real spreadsheet.

**Kumara_faith** · 10-04-2010, 02:22 AM

Hi Marvin,

The actual list of public holidays is as follows:

01/01/2008
10/01/2008
01/02/2008
07/02/2008
08/02/2008
20/03/2008
01/05/2008
19/05/2008
07/06/2008
01/09/2008
01/10/2008
02/10/2008
27/10/2008
08/12/2008
25/12/2008
29/12/2008
01/01/2009
26/01/2009
27/01/2009
02/02/2009
09/02/2009
09/03/2009
01/05/2009
09/05/2009
06/06/2009
31/08/2009
21/09/2009
22/09/2009
17/10/2009
27/11/2009
18/12/2009
25/12/2009
01/01/2010
01/02/2010
15/02/2010
16/02/2010
26/02/2010
01/05/2010
28/05/2010
05/06/2010
31/08/2010

Is there a way I can expand your formula to accomodate this ?

Thanks in advance and appreciate your valuable time and patience.

**MarvinP** · 10-04-2010, 04:31 AM

I'll have to scratch my head a while on this one. Can I write some VBA to do the problem or do you need it in Excel formulas only?

How about a countif function where you give the range? Are you using Excel 2007 or 2003?

The idea is to have a list of your holidays in a range somewhere. Then you would have a start date and end date. You would count how many dates in your holidays are Greater than the start AND Less than the end. You would subtract this from the days worked formula......

**Kumara_faith** · 10-04-2010, 04:42 AM

Hi Marvin,

i am using Excel 2007 and excel formula is preferable as I am not familiar with VBA.

Again, totally appreciate your expertise to expand the current formula into including the list of the public holidays as attached.

Appreciate your valuable time, patience and effort on this one.

**arthurbr** · 10-04-2010, 04:50 AM

haven't tested it, but there's a solution here

**Kumara_faith** · 10-04-2010, 04:57 AM

Hi arthurbr,

i actually tried the formula but it returns an incorrect value for the following scenario:

1) Start date and end date same as per public holiday ( returns as -1).Should return as 0
2) Start date and end date is a Saturday (returns as -1).Should return as 0.

Marvin's formula works perfectly except that I need the formula to include a long list of public holidays.

**daddylonglegs** · 10-04-2010, 05:22 AM

assuming A2 and B2 will always be "working days", i.e. not Saturdays or holidays then you should be able to use this formula

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

where holidays is a named range containing your holiday dates.

If A2 or B2 might be holidays/Sats then I think you need to define how that works.....would you be excluding the start day or the end day from the count?

**Kumara_faith** · 10-04-2010, 10:56 AM

Hi daddylonglegs,

As long as the dates are Saturday or Public Holidays, then it will need to be excluded from the count, regardless if it is the start date or end date.Example is as attached.

The following are addition criteria which the earlier Marvin formula already complies:
1) Start date and end date same as per public holiday =Should return as 0
2) Start date and end date is a Saturday =Should return as 0.

Appreciate your assistance.Please let me know if you need any additional information.

**daddylonglegs** · 10-04-2010, 11:21 AM

The problem here is that you aren't defining which days you are counting. 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?

You might say it dosen't matter....you can just subtract one.....but it does matter when you have the start or end day as a Sat or holiday. It's easy to see that the count should be zero if the start and end are on the same holiday but....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?

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?

**MarvinP** · 10-04-2010, 01:12 PM

Hi Kumara_faith,

Find the attached which will do what you want (I hope). The new work is below the first try.
I had to convert your Holiday List to Month/Day/Year format as you gave them in Day/Month/Year.
The method was to do a =Countifs formula to count all holidays after the start date AND before the end date.

I hope this works for you. There may be some problems if a person starts and ends on the same day or works less than a week. Please test my formula.

**MarvinP** · 10-04-2010, 01:30 PM

Hi Kumara,
I found your newer attachment and have updated it with a named range for the "holidays" range. This makes the formula look better and will allow you to add holidays easily. You'll have to update the named range if you add more holiday dates. Use the named range manager.

See the attached in 2007 file format.

**daddylonglegs** · 10-04-2010, 01:51 PM

Hello Marvin,

Your COUNTIFS suggestion is essentially doing the same as the SUMPRODUCT part in my formula, i.e. deducting holidays....but I'd take issue with the first part of the formula which presumably should calculate the number of non-Saturdays between the two dates. Your formula uses

=B21-A21-INT((B21-A21)/7)

but I believe that you can't make that calculation without knowing the day of the week of at least one of the dates, for instance the above will return a result of 9 if A21 and B21 are 10 days apart - e.g. 21st to 31st October 2010(assuming no holidays in the period), but a 10 day period could include either one Saturday or two, so depending on the start day the result should be 8 or 9.

My version does that, i.e.

=B21-A21-INT((WEEKDAY(A21)+B21-A21)/7)

**MarvinP** · 10-04-2010, 02:15 PM

Hey Kumara_faith,

I believe DL is correct on this point. Thanks to him for catching my error. I was a little worried about just skipping every 7th day in the count. I should have been concerned about starting on Friday and working 10 days, needing to subtract 2 days instead of just one.
I'll have to study DL's formula a little further. Weekday() returns a number from 1=Sunday to 7=Saturday.

So in my formula I should somehow count the number of Weekdays that equal 7 between the two dates.
I'll have to scratch my head on that one a while.

Also - Are any of the holidays Saturday? If they are we might be subtracting them twice.

Thanks to DL for keeping me honest on my WRONG formula. (I hate being wrong & thanks)

**daddylonglegs** · 10-04-2010, 02:59 PM

Originally Posted by MarvinP

Also - Are any of the holidays Saturday? If they are we might be subtracting them twice.

My suggested formula caters for that because the SUMPRODUCT part counts dates within the holiday range that are between the start and end dates....and are also not Saturdays.

A [moderately] well known way to count Saturdays between 2 dates is with SUMPRODUCT like this

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=7)+0)

and if you want you can change that to <> to exclude Sats and also exclude holidays, e.g.

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)))<>7)*(COUNTIF(holidays,ROW(INDIRECT(A2&":"&B2)))=0))

but that's a very inefficient method. Every date in the range is being examined twice, once to see whether it's a Saturday....and once to see whether it's a holiday.

......so a more efficient way to calculate the number of xdays between A2 and B2 (inclusive) is

=INT((WEEKDAY(A2-xday)+B2-A2)/7)

where xday is 1 (for Sun) through to 7 (for Sat)

Put that together with the SUMPRODUCT for subtracting holidays and you get this version

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

In Excel 2010 you can now use NETWORKDAYS.INTL to do all that more succinctly, i.e.

=NETWORKDAYS.INTL(A2,B2,17,holidays)

All 3 of those formulas in blue should give the same result......but for this particular question the one "fly in the ointment" is that the start or end day is excluded.....that shouldn't be a problem......just need a good specification and the above can be modified to suit.

Note: There are always ways to make these miscount. My preferred formula - no 2 above - may miscount if the same holiday dates are repeated, 1 and 3 won't.

**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)

**Kumara_faith** · 10-06-2010, 08:50 PM

daddylonglegs,

Thank you for the solution ! Appreciate your valuable time, effort and patience.

**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

Difference in Days Excluding Saturday and Public Holiday

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Re: Difference in Days Excluding Saturday and Public Holiday

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions