Help with overtime calculation

[Danie3107]

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 the way pay is worked out in my industry (actors)
Basically, they having different pay for day and night working.
I have the following to work out their day time pay.
Start time (H2) & finish time (I2) total hours (k2) contracted hours (J2)
Total hours formula =(I2-H2+(I2<H2))
Over time hours =MAX(0,K2-J2)
Now, days start from 7AM and ends at 12AM, where night hours start (and end at 6AM).
I’ve worked out a drop down menu to select when the night rate kicks in but what I’m struggling with is when the artist gets both day overtime and night over time.
For example, if my artist starts work at 7AM and then finishes at 2AM they will start getting day overtime at 7PM (after their 10 hour contract) and night overtime rate at 12AM
I basically want a formula that says stop calculating day overtime at 23:59 and start calculating night overtime at 00:00. I know I can just have other columns for ‘night start time’ and ‘night finish time’ but it’s be cool if my assistant directors didn’t have to do that and could just put in the start and end times.

[#2]

@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.

[#3]

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