Please help,
I want to calculate the actual downtime per each type of equipment and per each type of work order, as shown in the following table sample of the data which extend in real file to 6000 rows, the purpose of this that there is some work orders are performed in the same time so the downtime of the equipment must be for example 2 hours only if both these work orders are performed within these 2 hours as one fo the starts on 23.02.2020 11:00 to 12:00 and the other starts on 23.02.2020 11:30 to 13:00, so the downtime of this equipment shall start from 23.02.2020 11:00 till 13:00 which is equal only to 2 hours, so what is the formula used to calculate this and also to be fast as right now I am using the following formula and it takes a long time
to get the result, the formula as the following:
{=SUMPRODUCT((COUNTIFS(K2:K6000,"<"&MIN(K2:K6000)+ROW(INDIRECT("1:"&ROUND((MAX(L2:L6000)-MIN(K2:K6000))*1440,0)))/1440-1/2880,L2:L6000,">"&MIN(K2:K6000)+ROW(INDIRECT("1:"&ROUND((MAX(L2:L6000)-MIN(K2:K6000))*1440,0)))/1440-1/2880,G2:G6000,"*"&P3&"*",A2:A6000,"PRMN")>0)+0)/60}
as an example, the issue is there are too many orders generated to the same equipment, and the timing for these orders (Jobs) are overlapped, so my challenge actually is to calculate the total time for these jobs without overlapping, as shown in the following picture:
so here the total downtime is (7 hours if I make summation for the downtime), but What I need is a function to calculate remove the overlap and calculate the downtime as 3.5 hours only
Many Thanks, in advance.
Best Regards,
Mahmoud
