Forum Discussion

Danie3107's avatar
Danie3107
Copper Contributor
Sep 25, 2020

Help with overtime calculation

So, I’m a beginner in this, but I’ve managed to work out all formulas I have used so far, apart from this one. I’m creating a spreadsheet to help me do something called a ‘chit’ - it’s essentially ...
excel
Formulas and Functions
office 365
mtarler's avatar
mtarler
Silver Contributor
Sep 25, 2020

Danie3107  why not just do similar to what you already are doing and add the incremental additional pay for those hours.  In your example 7am-2am they are paid reg amt for 19 hours + delta for reg overtime for 7 hours (7pm - 2am) + delta for night overtime for 2 hours (12 - 2),  (BTW 7am - 7pm is 12 hours not 10 but we'll go with this as an example).  So lets say they get $10/hr (because actors are terribly under paid) and reg overtime is +20% so $12/hr and night overtime is +30% so $13/hr.  So in this case the formula is 19hrs*$10/hr + 7hrs*$2/hr + 2hrs*$1/hr.  Now you can combine terms and such in different ways to make the actual formula easier, for example you can:

= ( [total hours] + [all OT hrs]*[OT rate] + [night OT hrs]*[night OT rate - reg OT rate]) * [reg pay rate]

Hope that helps.

  • Danie3107's avatar
    Danie3107
    Copper Contributor
    Sep 25, 2020
    Whoops at the 10/12 hours. They’re paid different rates, some get overtime after 10 and some after 12, so apologies for that, I crossed both rates there!

    I will have a look at this suggestion and see if it works for me. At the moment we have just been adding the night overtime in an “additional box”.
    With it being friday, my brain is about to check out for the weekend 😉. Lol!

    Thanks again