Calculate overlap hours

Hello,

I am trying to determine the sum of total hours of downtime (trip) when there are 2 machines down during the same time period. To do this, I need to be able to determine if at any point of time there are 2 pieces of equipment down at the same time, and if this is true, how many hours were overlapped and that overlap is highlighted.

The attached spreadsheet shows how the data is presented. I managed to do this partially if the comparsion is done with two different consecutive equipments. But if we check date 29/7/2015, we will see that 4K-002 tripped on 29/7/2015 @ 06:00 PM until 30/7/2015 03:40 AM. In between 4K-001 overlapped 4 times (29/7/2015 @07:40 PM, 29/7/2015 @ 10:35 PM, 30/7/2015 @ 12:00 AM, 30/7/2015 @ 01:00) but it was counted only once because the formula checks the current row against the next row provided one is 4K-001 and the other is 4K-002. (these are highlighted in green)

Can someone help with this please..

Thanks in advance

Code for UDF

Please Login or Register  to view this content.

Thanks kvsrinivasamurthy for the interesting code. it showed me that my formula is wrong even though it worked some time.

I have made a mistake forgetting to unhide the columns between G:K. I actually wanted to show overlap time in each instance and highlight that as well similar to waht I did originally.

an this be done as well?

code for new UDF for individual over lap time.

Please Login or Register  to view this content.

Thankx kvsrinivasamurthy. This does the trick for me

Thanks for the feed back.