Working out interval between dates an times excluding sunday and public holidays.

[frazzle6]

Hi, have a spreadsheet that has 2 sets of dates and times on it. I added =(D2-C2) to a column called Interval and that's fine.

I have this on another tab that looks at public holidays =(NETWORKDAYS(C2,D2,'Public Holidays'!$B$2:$B$14))

This the next column =D2-C2+1 counts the days, i think correct me if i'm wrong.

This under a column heading Saturday =IF(WEEKDAY(D2)-WEEKDAY(C2)<=0,-1,0)-INT(I2/14) someone else gave me that not sure what it's doing

Last column is =(I2-H2+J2) which if there number in there take 24 hrs of the interval.

I need this take sunday and public hoildays into account and adjust the interval accordingly

 

[barry houdini]

Hello frazzle6, welcome to MrExcel,

So you want the total hours between C2 and D2 but excluding Sundays and holidays?

Can either of C2 or D2 be on a Sunday or holiday?

Which version of Excel are you using? If you have Excel 2010 or later you can use NETWORKDAYS.INTL function to exclude Sundays and holidays, e.g. this formula will give you the total hours (excluding Sundays and holidays) assuming C2 and D2 will not be on a weekend or holiday

=NETWORKDAYS.INTL(C2,D2,11,'Public Holidays'!$B$2:$B$14)-1+MOD(D2,1)-MOD(C2,1)

custom format result cell as [h]:mm

 

[frazzle6]

Hi Barry, C2 and D2 are date time fields, they go all the way down to C1667 and D1667, there are no Sunday or public holidays in those cells.

U'm using 2007 excel.

The problem when i use =(D2-C2), counts the hours between the dates, regardless. Can that function be modified to work on excel 2007.

If you would like the spreadsheet i can send it to you.

 

[barry houdini]

OK for Excel 2007 use this formula in row 2 copied down - format as [h]:mm

=D2-C2-INT((1-WEEKDAY(D2)+INT(D2)-INT(C2))/7)-1-SUMPRODUCT(('Public Holidays'!$B$2:$B$14>C2)*('Public Holidays'!$B$2:$B$14<D2)*(WEEKDAY('Public Holidays'!$B$2:$B$14)<>1))

 

[frazzle6]

HI, seems to work, fantastic if it does. Sunday hours and public holiday hours are not included in the interval?.

Is this what this does, i'm a novice so i need help, thanks so much

OK for Excel 2007 use this formula in row 2 copied down - format as [h]:mm

=D2-C2-INT((1-WEEKDAY(D2)+INT(D2)-INT(C2))/7)-1-SUMPRODUCT(('Public Holidays'!$B$2:$B$14>C2)*('Public Holidays'!$B$2:$B$14<d2)*(weekday(<font color="#333333">'Public Holidays'!$B$2:$B$14)<>1))

</d2)*(weekday(

 

[barry houdini]

Sorry, that formula got garbled - sometimes the board here thinks that < or > signs are HTML tags and the formula gets corrupted - should be this version

=D2-C2-INT((1-WEEKDAY(D2)+INT(D2)-INT(C2))/7)-1-SUMPRODUCT(('Public Holidays'!$B$2:$B$14>C2)*('Public Holidays'!$B$2:$B$14<d2)*(weekday(<font color="#333333" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">'< D2)*(WEEKDAY(Public Holidays'!$B$2:$B$14)<>1))

Yes, that should do exactly the same as the NETWORKDAYS.INTL version I posted - it counts all hours between C2 and D2 except Sunday and /or holiday hours</d2)*(weekday(

 

[frazzle6]

<d2)*(weekday(<font color="#333333" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Thanks for that, worked a treat and cleaned up the spread sheet with all the opther columns i had trying to get the correct interval.

Cheers.

PS how do mark this as solved?.</d2)*(weekday(