I'm trying to calculate worked hours with a single break time removed
The breaktime needs to be removed from the enhanced rate hours, this is between 6pm and 6am, if there is no time in the enhanced rate then it takes the time off the normal hours, but it may be that the break is 45 minutes long but 30 minutes of work has been done in the higher rate, so it wouls actually be 15 minutes from the normal rate.
For hours between 6am and 6pm I'm using
=24*((B5>C5)*MEDIAN(0,C5-1/4,3/4-1/4)+MAX(0,MIN(3/4,C5+(B5>C5))-MAX(1/4,B5)))
For hours before 6am or after 6pm im using
=24*((B5>C5)*MIN(C5,1/4)+MAX(0,MIN(1/4,C5+(B5>C5))-B5))+24*((B5>C5)*MEDIAN(0,B5-3/4,1/4)+MAX(0,MIN(1,C5+(B5>C5))-MAX(3/4,B5)))
For overtime (any working time over 9 hours)
=IF((C5-B5)*24>9,((C5-B5)*24-9),0)
Break time is in D5 and can be anything from 15 minutes to 60 minutes
Please does anyone know how to subtract the break time.
Thanks
Mak
Bookmarks