# EXCEL HELP

Copper Contributor

# EXCEL HELP

Hello,

I'm making a timesheet to help my employees accurately claim the correct amount of overtime/penalty rates.

As you can see above this is a normal start/finish time with a meal break and total hours worked, end of 8 hrs and end of 10hrs.

Working past 8 hours is time and half (150%) for the two hours after 8hrs (8-10), after 10hrs is double time (200%).

For example:

Start time is 0800 (8 AM)

Finish time is 1700 (5 PM)

This is 30 mins of OT.

My company works that out like this ".5hr of 150%".

My problem is, how do I get the cell K10 to self populate with ".5hr of 150%" when cells A5 says 0800 and B5 says 1700??

I understand that i could just use J5, but J5 doesnt calculate penalty rate.

Something like if A5 has this time & B5 have this time then K10 will tell me how much OT to claim.

# Re: EXCEL HELP

Hi @Drysie, you can do it including couple of parameters and following formula in K10

Parameters to determine hours at 150% and 200%. Last parameter is a unit of measure to calculate(1 hr).

In k10 you can include this formula :  =IF(J5-I15>0;+ROUND((J5-I15)/K15;2)&" of 150"; "")

The IF validate if you have more than 8 hours. Alfter that, calculate over time in minutes and compare with unit of measure to get a 0.5 corresponding to 30 minutes.

Please let me know if it works for you.

Regards