+ Reply to Thread
Results 1 to 5 of 5

Calculate between start and finish times to pay a shift penalty

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    19

    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!!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-30-2013
    Location
    Hobart, TAS
    MS-Off Ver
    Office 2003, XL2007
    Posts
    46

    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. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Audere est facere

  4. #4
    Registered User
    Join Date
    12-27-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    19

    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. #5
    Registered User
    Join Date
    12-27-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    19

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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