# How can I take break times from enhanced rate and overtime if applicable on a timesheet?

1. ## How can I take break times from enhanced rate and overtime if applicable on a timesheet?

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

Mak

3. ## Re: How can I take break times from enhanced rate and overtime if applicable on a timeshee

Hi

I'm not sure I explained it properly

Let me try again, along with the worksheet

I'm trying to calculate worked hours with a single break time removed in Excel 2003

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.

B5 contains start time and C5 contains end time

Cell E5 contains the following formual to calculate hours between 6am and 6pm

=24*((B5>C5)*MEDIAN(0,C5-1/4,3/4-1/4)+MAX(0,MIN(3/4,C5+(B5>C5))-MAX(1/4,B5)))

Cell F5 contains the following formual to calculate hours before 6am or after 6pm

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

Cell G5 contains the following formula to calculate overtime (any working time over 9 hours)

=IF((C5-B5)*24>9,((C5-B5)*24-9),0)

I now need to deduct the break time entered in D5 (this can be anything from 15 minutes to 60 minutes) from the highest rate, so before 6am and after 6pm is enhanced rate, so the break time needs to be deducted from this, however, this may be anything from 15 minutes up, if less than the break time the remainder would need to be deducted from the 6am to 6pm standard rate time.

Sometimes no enhanced rate is worked so the whole of the break time would be deducted from the standard rate time.

Thanks

Mak

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1