Return a OT (OT01,OT02) Hours as per Condition.

Hi,


The formula help is needed to return a result for columns F and G based on the start and finish times specified in columns D and E.

The OT01 hours should be calculated below the finish time at 9 PM in actual working days.

As stated in sample results, OT02 is calculate after 9 PM and the total number of hours spent on Sundays and holidays comes under OT02.


Sample sheet with expected results are attached.


Thanks,

Sample amended enclosed.

Many thanks for the your formula solution,

To demonstrate the issue, I changed the start and finish times of the sample sheet for 04-02-2023 to 5:30 PM and 12:30 PM the next day. For row 11, the results should be OT-01 05 hours and OT-02 15.5 hours, but instead they return OT-01 -5 hours and OT-02 24 hours.

Furthermore, I did the same for Rows 14 and 16, where the OT-01 and OT-02 hours were not returned as required.

Sample sheet is attached.

Thanks,

Try, based on workbook in post #1
G6=IF(OR(C6="Sunday",D6>0,AND(E6>TIME(21,0,0),F6<TIME(21,0,0))),0,IF(F6<E6,MOD(TIME(21,0,0)-E6,1),MOD(MIN(TIME(21,0,0),F6)-E6,1))*24)
H6=MOD(F6-E6,1)*24-G6