Calculate OVERTIME DURATION in specific time frame

[Intalzky]

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

[Vikas_Gautam]

Got the solution..
I was a bit tricky , but I managed...
check the attachment...



Don't forget to click *

[Intalzky]

Hi Vikas,

The Formula worked. This is exactly what I need. Thank you!

[dalahans]

Hi. When i click download on the file, I get the file "attachment.php"
How do I download your excelfile?

[Vikas_Gautam]

I am able to download the file
I am attaching it here again