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

unipulse1

unipulse1

New member
Joined
Dec 20, 2013
Messages
1
Reaction score
0
Points
0
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.

DayStart 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
Mon05:0016: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)"
Mon16:3023:00"=(E4/0.041666667)-(D4/0.041666667)"00"=F4":eyebrows:HELP!!!:eyebrows:"=I4-J4"
 

Attachments

  • KZN-HoursSummaryTemplate.xlsx
    9.7 KB · Views: 16
Kzn-HoursSummary-Amended-screengrab.jpg

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
 

Attachments

  • KZN-HoursSummaryTemplate-Amended.xlsx
    11.4 KB · Views: 10
You must log in or register to reply here.
Back
Top