Calculate the Actual down time for equipment

%3CLINGO-SUB%20id%3D%22lingo-sub-1354383%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20the%20Actual%20down%20time%20for%20equipment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1354383%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639532%22%20target%3D%22_blank%22%3E%40Mahmoud_Hazem%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20attached%20the%20sample%20file%2C%20to%20look%20into%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1343104%22%20slang%3D%22en-US%22%3ECalculate%20the%20Actual%20down%20time%20for%20equipment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1343104%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22x-hidden-focus%22%3EPlease%20help%2C%3CBR%20%2F%3EI%20want%20to%20calculate%20the%20actual%20downtime%20per%20each%20type%20of%20equipment%20and%20per%20each%20type%20of%20work%20order%2C%20as%20shown%20in%20the%20following%20table%20sample%20of%20the%20data%20which%20extend%20in%20real%20file%20to%206000%20rows%2C%20the%20purpose%20of%20this%20that%20there%20is%20some%20work%20orders%20are%20performed%20in%20the%20same%20time%20so%20the%20downtime%20of%20the%20equipment%20must%20be%20for%20example%202%20hours%20only%20if%20both%20these%20work%20orders%20are%20performed%20within%20these%202%20hours%20as%20one%20fo%20the%20starts%20on%2023.02.2020%2011%3A00%20to%2012%3A00%20and%20the%20other%20starts%20on%2023.02.2020%2011%3A30%20to%2013%3A00%2C%20so%20the%20downtime%20of%20this%20equipment%20shall%20start%20from%2023.02.2020%2011%3A00%20till%2013%3A00%20which%20is%20equal%20only%20to%202%20hours%2C%20so%20what%20is%20the%20formula%20used%20to%20calculate%20this%20and%20also%20to%20be%20fast%20as%20right%20now%20I%20am%20using%20the%20following%20formula%20and%20it%20takes%20a%20long%20time%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3Eto%20get%20the%20result%2C%20the%20formula%20as%20the%20following%3A%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%7B%3DSUMPRODUCT((COUNTIFS(K2%3AK6000%2C%22%26lt%3B%22%26amp%3BMIN(K2%3AK6000)%2BROW(INDIRECT(%221%3A%22%26amp%3BROUND((MAX(L2%3AL6000)-MIN(K2%3AK6000))*1440%2C0)))%2F1440-1%2F2880%2CL2%3AL6000%2C%22%26gt%3B%22%26amp%3BMIN(K2%3AK6000)%2BROW(INDIRECT(%221%3A%22%26amp%3BROUND((MAX(L2%3AL6000)-MIN(K2%3AK6000))*1440%2C0)))%2F1440-1%2F2880%2CG2%3AG6000%2C%22*%22%26amp%3BP3%26amp%3B%22*%22%2CA2%3AA6000%2C%22PRMN%22)%26gt%3B0)%2B0)%2F60%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%228e6ae6cc-1584-4afc-bd31-bb901404980e.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F187116iAD54D4299B7A7753%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%228e6ae6cc-1584-4afc-bd31-bb901404980e.jpg%22%20alt%3D%228e6ae6cc-1584-4afc-bd31-bb901404980e.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eas%20an%20example%2C%20the%20issue%20is%20there%20are%20too%20many%20orders%20generated%20to%20the%20same%20equipment%2C%20and%20the%20timing%20for%20these%20orders%20(Jobs)%20are%20overlapped%2C%20so%20my%20challenge%20actually%20is%20to%20calculate%20the%20total%20time%20for%20these%20jobs%20without%20overlapping%2C%20as%20shown%20in%20the%20following%20picture%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%228e6ae6cc-1584-4afc-bd31-bb901404980e.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F187117iBB19E34B241C3B2E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%228e6ae6cc-1584-4afc-bd31-bb901404980e.jpg%22%20alt%3D%228e6ae6cc-1584-4afc-bd31-bb901404980e.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eso%20here%20the%20total%20downtime%20is%20(7%20hours%20if%20I%20make%26nbsp%3B%20summation%20for%20the%20downtime)%2C%20but%20What%20I%20need%20is%20a%20function%20to%20calculate%20remove%20the%20overlap%20and%20calculate%20the%20downtime%20as%203.5%20hours%20only%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMany%20Thanks%2C%20in%20advance.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBest%20Regards%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMahmoud%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1343104%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1354644%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20the%20Actual%20down%20time%20for%20equipment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1354644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%2C%20I%20attached%20the%20file%20to%20the%20main%20post%2C%20many%20thanks%20for%20your%20support.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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}

 8e6ae6cc-1584-4afc-bd31-bb901404980e.jpg

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:

 

8e6ae6cc-1584-4afc-bd31-bb901404980e.jpg

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

 

3 Replies

Hi @Mahmoud_Hazem 

 

Please attached the sample file, to look into the query.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

@Faraz Shaikh, I attached the file to the main post, many thanks for your support.

Hi Mahmoud, I have a similar question. Did you get the solution for this yet?