timesheet calculation formula

Goodday and have a nice everyone..

I still beginner with excel and i cant manage to complete the timesheet after spent a few days trying.. and for your additional information, our office still using Microsoft Excel 2013..

Problem (1)
Refer Sheet TRIAL, and OT 2.0
Attachment 886551
OT 2.0 and OT 3.0 is for PH (public holiday only),
if they work form 8am till 12pm, they will get for 4hrs on OT 2.0 (red words),
if they work form 8am till 1pm, they STILL get for 4hrs on OT 2.0 because 1hrs Lunch Break (blue words),
if they work form 8am till 5pm, they will get for the total hours DEDUCT 1hr Lunch Break on OT 2.0 (green words),
if they work more than 6pm, they will get for the total hours DEDUCT 1hr Lunch Break on OT 2.0.

but the value OT hours that on red, blue and green colours not get same like "Supposedly OT 2.0 hours need"..



Problem (2)
Refer Sheet JAN25, and OT 1.5
Attachment 886552
Please refer Yellow Row that appeared as ##### error value..

OT 1.5 only works for Monday to Friday OT 1.5 after 5pm,
and Saturday OT 1.5 will start after 1pm, in other hand..

The formula got problem too..
if they work form 8am till 1pm, they will NOT get OT 1.5 but daily basic,
if they work form 8am till 2pm, they will get OT 1hr on OT 1.5 (total working hours DEDUCT 4 hours basic DEDUCT 1 hour Lunch Break).


herewith the excel for your references..
template.xlsx