Hours/Mins Rounding

[spencer1025]

[/URL][/IMG]

In an attempt to eliminate accidental overtime in my company, I need help making a formula that will accomplish a time calculation and round correctly based on my employer’s minute breakdown of an hour. Reg. time is 7.5 hrs.

Our minute breakdown is as follows (A)
A
1-3 = 0.00, 4-9 = .10, 10-15 = .20, 16-21 = .30, 22-27 = .40,
28-33 = .50, 34-39 = .60, 40-45 = .70, 46-51 = .80, 52-57 = .90,
58-59 = 1.0

I also found two other companies using different breakdowns (B and C):
B
1-6 = .1, 7-13 = .2, 14-19 = .3, 20-26 = .4, 27-33 = .5,
34-39 = .6, 40-47 = .7, 48-54 = .8, 55-59 = .9.

C
00-02 = .0, 3-8 = .1, 9-14 = .2, 15-20 = .3, 21-27 = .4,
27-32 = .5, 33-38 = .6, 39-44 = .7, 45-50, 51-56 = .9,
57-59 = .1.0,

I have tried using a timesheet calculator to avoid OT (Time Sheet Calculator), but it will not always round exactly how it appears on my timesheet. I'm not sure if the breakdown that my company is giving us is matching with Kronos, so I'm hoping I can get help using excel. Thank you!

 

[martindwilson]

with time difference expressed as hh:mm in a1
=TEXT(A1,"hh")+LOOKUP(LEFT(TEXT(A1,"mm:ss"),2)+0,{0,4,10,16,22,28,34,40,46,52,58},{0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1}) will give a decimal value of hours
so
07:14:00 would be 7.20 hrs

 

[spencer1025]

with time difference expressed as hh:mm in a1
=TEXT(A1,"hh")+LOOKUP(LEFT(TEXT(A1,"mm:ss"),2)+0,{0,4,10,16,22,28,34,40,46,52,58},{0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1}) will give a decimal value of hours
so
07:14:00 would be 7.20 hrs

Thanks for creating this formula. Base in the spreadsheet I’d posted, I was trying to create a formula that will sum-up the time for columns D-E. We have a new timesheet system (Kronos-online) that complicates how it rounds. I had pasted my timesheet into Excel to create formulas, but I don’t know how to. I tried your formula, but I’m still confuse

I can’t understand how Kronos rounded the time for:
1) D5 (sick time) and D6-E7 = 7.52
2) D14-E15 =7.6
3) D18-E19 (sick time) =7.55

Please help

 

[martindwilson]

never heard of kronos so i cant possibly guess how time is rounded and i dont see how anyof the calculations are made from that example