Calculating additional time outside of a prescribed range

Hi Good Folks,

Brand new member, humbly needing some assistance..!

I need to create a spread sheet that will allow my company to manage the work hours of its technical staff. We have technicians that are on permanent standby. The normal working day is from 08:00 am to 16:30 pm. However, technicians are often called to a breakdown late in the day, and will work beyond 16:30 pm. The challenge is that the times are recorded on the job cards as time started (say 15:45 pm) and time finished (say (20:15 pm). The overtime pay is calculated based upon the time spent on site from 16:30 pm.

Totaling the normal work hours is within my limited reach, however, calculating and totaling the hours after 16:30 is beyond me. Can anyone please help..??

Thanks
Kevin

try someting like this

=if([stop time - start time]<=8,([stop time - start time])*[hourly rate],([stop time - 16:30])*[overtime rate]+[hourly rate]*8)

in the true section regular time is all that is calculated in the false section overtime is calculated and the regular time is added back in. I hope this answers your question.

Thanks Motu040602,

I will play with this as it appears a lot more efficient than what I have, in my limited ability, done so far. Initially, we just need to calculate and total the regular and overtime hours, as the rates also vary depending on the location and nature of each job. (ie. whether on a ship, or underground etc.)

If your overtime hours are 1.5 time you regular time hours you can take the [rates] out of the equation above and just replace the [overtime rate] with 1.5 to get a good multiplier for base rate at any location. Good luck!

Would this work for the hourly rate whereby I can replace [hourly rate] with 1 ..??

Sure you can replace [hourly rate] with 1 and it will just show the hours worked or you can remove *[hourly rate] completely because anything time 1 doesn't change. By removing the rates you just leave behind the hours need to be paid. This seems a bit strange to people figuring OT sometimes, so let me explain a bit more.

Lets say our rate is $10/hr (keep the math simple)
And a 50 hour week (if you need to pay by the day just use you days hours)

so 40 hours of normal time times $10/hr is $400
Plus the 10 OT hrs times $15/hr is $150
Total of $550

But We could just take the OT hours times 1.5 instead so 10*1.5=15 plus our 40 regular hours= 55hours of pay
55 * 10 is $550 same answer (this is how the above equation works).

Thanks Motu040602,

We actually calculate the pay utilising the company proprietary software. It just doesn't allow us to accurately differentiate between regular hours and overtime hours. This has helped me tremendously, and I'm grateful for your quick and thorough response. Thank You..!

Regards

Kevin

Thank you and good luck with your projects.