# Calculate between start and finish times to pay a shift penalty

1. ## Calculate between start and finish times to pay a shift penalty

Hi,

I am trying to work out a formula that will calculate a shift penatly between a start and finish time.
The penalty is and early shift penalty so if an employee works between midnight and 7am then they should get \$2.20 for each hour worked between these times.
I have attached the workbook I am working on.

Start time is in cell M1637, Finish time is in N1637, total hours worked is P1637.
I am trying to write the formula in Cell AK1637, and the reference fot the \$2.20 is in cell Ak1635.

Miss seems to be if the employee starts the shift before midnight but finishes after, the formula I have already does not calculate correctly.

Please help!!

2. ## Re: Calculate between start and finish times to pay a shift penalty

This does what you want
M1637 =starttime, N1637 -finishtime
P1637=IF(M1637>N1637,(1+N1637),N1637)-M1637 (TOTAL)
AK1637 =IF(N1637>TIME(7,0,0),N1637-TIME(7,0,0),N1637)*AK1635*24 (

3. ## Re: Calculate between start and finish times to pay a shift penalty

Try this formula in AK1618 copied down

=(MOD(N1618-M1618,24)-(M1618>N1618)*17-MAX(N1618,7)+MAX(M1618,7))*AK\$1616

4. ## Re: Calculate between start and finish times to pay a shift penalty

Hey Daddylonglegs,

You are a bloody legend!!! Thank you so much, I have been racking my brain with my coleague trying to get this to work for two days now. Thank you again!!!!

5. ## Re: Calculate between start and finish times to pay a shift penalty

Hi M E Good,

Not really sure how to use the formula that you have written? Seems incomplete. But thank you very much for having a crack!!

Cheers

#### Thread Information

##### Users Browsing this Thread

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