Exclude Overlap between date and time in excel and calculate Down Time

Kukku

New Member
Joined
Feb 23, 2019
Messages
1
ABCDEFGHIJKLM
2SN.ADTActual DTOverlap with (for representation only)SN.BDTActual DTOverlap with (for representation only)
3StartEndStartEnd
4111-02-2019 16:50:0012-02-2019 02:30:005800overlap with SN.5 in A111-02-2019 16:50:0012-02-2019 19:27:0015970overlap with SN.5 in A
5211-02-2019 14:50:0011-02-2019 15:20:00300overlap with SN.5 in A200
6311-02-2019 13:10:0011-02-2019 14:30:00800overlap with SN.5 in A300
7411-02-2019 11:18:0012-02-2019 09:00:0013020overlap with SN.5 in A400
8511-02-2019 11:17:0012-02-2019 19:27:0019301930500
9611-02-2019 11:15:0012-02-2019 11:15:0014400overlap with SN.7 in A600
10711-02-2019 09:50:0011-02-2019 16:50:0042087after overlap with SN.5 in A700
11800800
12
13SN.CDTActual DTOverlap with (for representation only)SN.DDTActual DTOverlap with (for representation only)
14StartEndStartEnd
15111-02-2019 14:50:0011-02-2019 15:50:00600overlap with SN.5 in A111-02-2019 11:20:0011-02-2019 15:30:002500overlap with SN.5 in A
16211-02-2019 08:30:0011-02-2019 08:45:001515no overlap200
17300300
18400400
19500500
20600600
21700700
22800800

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Data
Hi,
Please Help guys,
I have attached the sample screenshot of the excel sheet which have different date andtimings (dynamic depending upon the search criteria)
1. A,B, C, D are timing of different activities fetching from master sheet,condition based.
2. Ifyou see the activities have individual Down Time (DT)
3. Butthere are date and time overlap within and between activities which affects ActualDT
4. Ineed to find the actual down time by avoiding overlap between date and timeranges and time breaks (by time break I meant no activity available forparticular duration, Gaps between one activity and other)
5. Asof now it is calculated manually by finding the biggest DT in the category and comparingother activities against that for overlap and breaks.
6. Sampleis manually updated in the excel.
7. ForEg. - For item A total DT is 1930 + 87 = 2017 (given in excel)
8. Ais the important activity, hence at first DT of A is calculated and otheractivities are compared against item A
9. Alsoindividual activity (A, B, C, D) DT is also required to split up (to identifytotal contribution of each activity after excluding overlaps and time breaks)
10. Soit is calculated manually as follows first A (excluding overlap and breakswithin), then B (excluding overlap and breaks within and then excluding overlapwith A), then C (excluding overlap and breaks within and then excluding overlapwith B and A), then D (excluding overlap and breaks within and then excluding overlapwith C, B, A)
11. Sothe actual DT in the attached excel is 1930 + 87 + 15 = 2032 instead of 7704,which is the sum of individual timing DT.
12. Thedate and timing can come in any combination.
13. Onlything is that the start date comes in the order of last comes first.
14. Maxof 8 activities per category (A, B, C, D) can come.
Can anyone helpon this, manual calculation is hectic and takes considerable time, as like thisthere are many different combinations to calculate depends of the conditionsgiven as said in SN.1
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,108,971
Messages
5,525,967
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top