counting specific hours between two times

[each]

Please can someone help improve on this forumla. At the moment it works for all times except midnight. I am also sure that there must be a more efficient way of writing it. Basicly we pay single rate between 8.00am and 8.00pm at all other times and on bank holidays and weekdays we pay an enhanced rate. If I can calculate the standard hours then the remainder of the hours are enhanced.

Aim to count how many work hours fall between a standard start time (SHS) and a standard end time (SHE) (currently 8.00am and 8.00pm), that do not fall on a weekend and are not a bank holiday.

Column B = Start time (eg 06:00)
Column C - End time (eg 22:00)

Column D= date (from which day of week is derived)
Column E = Y or N for whether it is a bank holiday or not.

Current forumla in column P is
=(IF(ISNONTEXT(D11),(IF(OR(WEEKDAY(D11)=1,WEEKDAY(D11)=7,E11="y"),0,IF(B11<shs,IF(C11<=shs,0,MIN(she,C11)-shs),0)+IF(AND(B11>=shs,B11<she),(O11/24)-IF(AND(C11>B11,C11>she),C11-she,0)-IF(C11<B11,MIN(C11,shs)+4/24,0),0)+IF(B11>=she,IF(AND(C11<B11,C11>=shs),MIN(C11,she)-shs,0),0))+A11),0)*24)-R11

Some of our shifts start of end at midnight so I do need the formula to work for that time as well as other times.


Thanks
E

nb for a start time of 6.00am and an end time of 22.00 the formula should calc standard hours of 12

for a start time of 3.00am and end time of 10am the formula should count 2

for a start time of 15:00 and and end time of 24:00 the forumla should count 5.

[daddylonglegs]

Hello E,

I assume, then that none of the shifts cross midnight, e.g. 21:00 - 08:00?

If that's the case then this formula will get the hours between shs and she

=IF(OR(WEEKDAY(D11,2)>5,E11="y"),0,(MIN(C11,she)-MAX(B11,shs))*24)

For midnight as a start time input as 00:00 or as an end time 24:00

[each]

Dear DaddyLL

Thank you so much for your help.

No shifts do not go on after midnight (or on the odd occasion that they might they can record that time on the next day).

I still have a problem if the shift starts after 20:00 - the formula is returning a negative number.

The same applies if some one is called out in the early hours to do say a 3:00am to 5:00am call out.


thank you soooooo much.

E

[daddylonglegs]

Ah, sorry, I missed out a crucial MAX, try this version

=IF(OR(WEEKDAY(D11,2)>5,E11="y"),0,MAX(MIN(C11,she)-MAX(B11,shs),0)*24)

[each]

Thanks for your help Mr LL.
I think I have it working now.

E