Forum Discussion

Drysie's avatar
Drysie
Copper Contributor
Jul 04, 2023

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.

excel
  • leoperdia's avatar
    leoperdia
    Brass Contributor
    Jul 04, 2023

    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

    My UpWork profile 

     

     

     

Resources