Calculating Shift Premium Hours

Hi,

I have attached a sample spreadsheet which I am building to calculate an employees shift premium payment each day they work.

For example any hours worked between the following hours attract a payment premium as detailed...

06:00 to 18:00 - no premium (day)
18:00 to 22:00 - 10% premium (late)
22:00 to 06:00 - 20% premium (night)

Issue I have is that the formulas have a few glitches that I can't seem to resolve i.e.

1. 08:00 to 20:00 is allocating 30 minute premium at night rate should only be late premium for two hours
2. 19:00 to 20:00 is allocating only 30 minutes of night premium and it should be one hour of late premium

Any help would be very grateful as I am looking to use this sheet today and need to resolve these ASAP for our HR department!!!

Thanks

Steven

Found my own erro!

formula changed for the late shift from ...

=FLOOR(SUM(IF(H8>I8,TRUE,FALSE)*MEDIAN(0,I8-$E$3,1/3)+MAX(0,MIN($E$4,I8+IF(H8>I8,TRUE,FALSE))-MAX($E$3,H8))),"00:30")

To =SUM(IF(H8>I8,TRUE,FALSE)*MEDIAN(0,I8-$E$3,1/3)+MAX(0,MIN($E$4,I8+IF(H8>I8,TRUE,FALSE))-MAX($E$3,H8)))

All works fine now.

Thanks to anyone who may have looked or had a go at this for me!

Steven