Overtime calculator based on various rules i.e. day, hours worked, callout payment

Hello,

I am trying to create a spread sheet to calculate overtime but there are a few rules that I don't know how to apply in excel. This spreadsheet is only looking at overtime hours and is not concerned with normal working hours.

The rules for the overtime are as follows:

Monday (06:00) - Saturday (14:00) = OT rate 1 (time & half)
Saturday (14:00) - Monday (06:00) = OT rate 2 (double time)

If the overtime is a call out then the following rules also apply:

Call-out rates: i) 1 hour paid at OT rate for the hour called out AND ii) Payment for 3 hours at OT rate, or actual overtime hours worked, whichever is greatest.

Any help would be much appreciated.

Thanks

Rhod

Glenn,

Thanks for the reply.

I have attached an example of what I have so far. I would like the pay to be calculated automatically from inputting only the date, whether it's a call out or not & hours worked.

Thanks

Rhod

Overtime record.xlsx

it would be Monday 06:00 - Saturday 13:59 = OT1 & Saturday 14:00 - Monday 05:59 = OT2