Calculate OVERTIME DURATION in specific time frame

Hello,

I need help in calculating the overtime duration of each employees. Common formula that I knew was the (end time - start time). However, the scenario here is that our company is operating 24 hours a day 7 days a week. and there is 2 types of Hours of Operation. Normal Hours which is 6:00 AM -6:00 PM and the After Hours which is 6:00 PM - 6:00 AM.

I need to calculate the overtime rendered by employees in a separate Operation type. If the employee rendered overtime between 6:00 AM -6:00 PM it falls under the Normal hours and vice versa. The twist here is, some employees are rendering overtime overlapping with the 2 types of operation. For example employee rendered OT from 5:00 PM - 8:00 PM then Normal hours should have 1:00 and After Hours should have 2:00 total of 3:00 hours OT.

Attached is the example for your reference.

Thank you in advance for your help.OT.xlsx