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

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

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 23:59 Stop time Always type '24:00' AS 23:59 Total Hrs Fraction Default Normal Hours Hrs @ 1 Fraction Total O/Time Fraction Hrs @ 1.5 Fraction maximum 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"  Reply With Quote

2. 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  Reply With Quote

#### Tags for this Thread

max value, multiple if formula #### Posting Permissions

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