Results 1 to 2 of 2

Thread: Clockcard 2 ins/outs p/day. Max o/time rate @1.5 rate = 6hrs, Bal o/time hours is @ 2

  1. #1

    Question Clockcard 2 ins/outs p/day. Max o/time rate @1.5 rate = 6hrs, Bal o/time hours is @ 2

    Register for a FREE account, and/
    or Log in to avoid these ads!

    In the table below my problem is working out the formula for the second clock-in-out on the same day.
    The maximum overtime per day rate @ 1.5 = 6 hours. Anything over 6 hours overtime per day = @ 2 (double rate).
    Been going round in circles here! please help. Thank you.

    Day Start time
    Always type
    '24:00' AS
    Stop time
    Always type
    '24:00' AS
    Total Hrs
    @ 1
    Hrs @ 1.5
    hrs = 6
    Hrs @ 2 Fraction
    Any hrs <6 is
    @ double rate
    Mon 05:00 16:00 "=(E3/0.041666667)-(D3/0.041666667)" 9 "=IF(OR(M3="AL",O3="SL",Q3="FL",S3="UL",U3="PH1",W3="PH2"),0,IF(F3>9,9,F3))" "=IF(H3>0,F3-H3,0)" "=IF(I3>6,6,I3)" "=IF(I3>6,I3-6,0)"
    Mon 16:30 23:00 "=(E4/0.041666667)-(D4/0.041666667)" 0 0 "=F4" HELP!!! "=I4-J4"
    Attached Files Attached Files

  2. #2
    Click image for larger version. 

Name:	Kzn-HoursSummary-Amended-screengrab.jpg 
Views:	10 
Size:	60.1 KB 
ID:	1919

    The easy way is to include a new column between stop time and total hours

    Which adds up the total hours for a day. (if the date is the same as in the previous row, it adds the previous row's cumulative total hours to the latest row's. If not, (i.e. this is the first row for a new day, it only counts this row's hours)

    The next column, the fraction column, only does it's stuff if the current row is the last row for a given day.

    The other columns, that work out how many hours at what rate, only do their stuff if there is a number in teh total hours (Fraction) cell for the current row
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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