Help me in time calculation

excel4u · 03-19-2009, 05:18 AM

Hi guys
Q 1
I have data like this
A1 ---- Start Date/Time ---- 3/1/2009 8:30
B1 ---- End Date/Time ---- 3/4/2009 09:45
I have to calculate hours b/w these time exculuding only sunday and holiday

Q2
I have to also calculate tat between these date exculuding only sunday and holiday

**xld** · 03-19-2009, 07:30 AM

Try

=((INT(B1-1)-INT(A1+1)+1
-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(B1-1)&":"&INT(A1+1))))=1)))
-SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(INT(B1-1)&":"&INT(A1+1))),holidays,0))))
+(AND(NOT(ISNUMBER(MATCH(A1,holidays,0))),WEEKDAY(A1)<>1))*(1-MOD(A1,1))
+(AND(NOT(ISNUMBER(MATCH(B1,holidays,0))),WEEKDAY(B1)<>1))*(MOD(B1,1)))*24

**Simon Lloyd** · 03-19-2009, 08:07 AM

xld, nice lengthy formula, however i suspect the op meant the likes of public holidays, i may be wrong, but as he didn't specify that there is a range of dates named holidays or how he distinguishes employees' holidays i think the formula will only get him half way there. As always (and i've been guilty of it) there is probably more to the question with the ever moving goal posts.

**xld** · 03-19-2009, 09:30 AM

Originally Posted by Simon Lloyd

xld, nice lengthy formula, however i suspect the op meant the likes of public holidays, i may be wrong, but as he didn't specify that there is a range of dates named holidays or how he distinguishes employees' holidays i think the formula will only get him half way there. As always (and i've been guilty of it) there is probably more to the question with the ever moving goal posts.

I am sure that you are right, but it is a self-help world - put the holidays in a named range called holidays. I ain't going to try and calculate all future possible holidays across the world in an Excel function, I'll be dead before it's finished.

**Simon Lloyd** · 03-19-2009, 10:10 AM

Originally Posted by xld

I am sure that you are right, but it is a self-help world - put the holidays in a named range called holidays. I ain't going to try and calculate all future possible holidays across the world in an Excel function, I'll be dead before it's finished.

Hmmm. not like you to shy from such a trivial challenge!

excel4u · 03-20-2009, 03:36 AM

Hi xld

how can exclude public holiday if any.

**Simon Lloyd** · 03-20-2009, 07:27 AM

As xld said, just add the dates to a named range called holidays! the formula will then work!

**daddylonglegs** · 03-20-2009, 08:05 AM

Can the start/end dates be Sundays or holidays? Your example shows 3/1/2009 is that 1st March or 3rd January, 1st March was a Sunday......

If start and end dates can't be Sundays or holidays you can use this formula to calculate hours

=B1-A1-INT((WEEKDAY(A1-1)+INT(B1)-INT(A1))/7)-SUMPRODUCT((holidays>A1)*(holidays<B1)*(WEEKDAY(holidays)<>1))

where holidays is a named range containing holiday dates

format result cell as [h]:mm

If start/end dates can be any date then try

=B1-A1-INT((WEEKDAY(A1-1)+INT(B1)-INT(A1))/7)-SUMPRODUCT((holidays>=INT(A1))*(holidays<=B1)*(WEEKDAY(holidays)<>1))+IF(OR(WEEKDAY(A1)=1, COUNTIF(holidays,INT(A1))),MOD(A1,1))+IF(OR(WEEKDAY(B1)=1,COUNTIF(holidays,INT(B1))),1-MOD(B1,1))