Calculate Overtime

Dear Forum Members,

I require your help with the following:

EXPLANATION OF MY PROBLEM:

I am paid for Voluntary Overtime separately to any Rostered Overtime.
Rostered Overtime is considered part of the total amount of hours worked in a shift.
For example, the normal shift time worked is 8 hrs, but in some cases, the shift time is 9 hours. Therefore 8 hours is paid at the normal rate, and 1 hour, considered as rostered overtime, is paid at 1.5.
However Voluntary Overtime is treated separately. It is not added to normal shift time.

THE SOLUTION REQUIRED TO SOLVE MY PROBLEM WITH VOLUNTARY OVERTIME:

I require a formula/s that will calculate the first 3 hours at 1� and the balance, that is after 3 hours, calculated for double time.

Please find attached a spreadsheet for your reference.

Thank you .. Michael

This will work out the total number of digital hours:

=LET(d,B3*24,dt,IF(d>3,(d-3)*2,0),ht,IF(d>3,3*1.5,d*1.5),ht+dt)

Then multiply this by the hourly rate.

This, then:

=IF(C4>3,3,C4)