| A | B | C | D | E | F | G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2 | SN. | A | DT | Actual DT | Overlap with (for representation only) | SN. | B | DT | Actual DT | Overlap with (for representation only) | |||
| 3 | Start | End | Start | End | |||||||||
| 4 | 1 | 11-02-2019 16:50:00 | 12-02-2019 02:30:00 | 580 | 0 | overlap with SN.5 in A | 1 | 11-02-2019 16:50:00 | 12-02-2019 19:27:00 | 1597 | 0 | overlap with SN.5 in A | |
| 5 | 2 | 11-02-2019 14:50:00 | 11-02-2019 15:20:00 | 30 | 0 | overlap with SN.5 in A | 2 | 0 | 0 | ||||
| 6 | 3 | 11-02-2019 13:10:00 | 11-02-2019 14:30:00 | 80 | 0 | overlap with SN.5 in A | 3 | 0 | 0 | ||||
| 7 | 4 | 11-02-2019 11:18:00 | 12-02-2019 09:00:00 | 1302 | 0 | overlap with SN.5 in A | 4 | 0 | 0 | ||||
| 8 | 5 | 11-02-2019 11:17:00 | 12-02-2019 19:27:00 | 1930 | 1930 | 5 | 0 | 0 | |||||
| 9 | 6 | 11-02-2019 11:15:00 | 12-02-2019 11:15:00 | 1440 | 0 | overlap with SN.7 in A | 6 | 0 | 0 | ||||
| 10 | 7 | 11-02-2019 09:50:00 | 11-02-2019 16:50:00 | 420 | 87 | after overlap with SN.5 in A | 7 | 0 | 0 | ||||
| 11 | 8 | 0 | 0 | 8 | 0 | 0 | |||||||
| 12 | |||||||||||||
| 13 | SN. | C | DT | Actual DT | Overlap with (for representation only) | SN. | D | DT | Actual DT | Overlap with (for representation only) | |||
| 14 | Start | End | Start | End | |||||||||
| 15 | 1 | 11-02-2019 14:50:00 | 11-02-2019 15:50:00 | 60 | 0 | overlap with SN.5 in A | 1 | 11-02-2019 11:20:00 | 11-02-2019 15:30:00 | 250 | 0 | overlap with SN.5 in A | |
| 16 | 2 | 11-02-2019 08:30:00 | 11-02-2019 08:45:00 | 15 | 15 | no overlap | 2 | 0 | 0 | ||||
| 17 | 3 | 0 | 0 | 3 | 0 | 0 | |||||||
| 18 | 4 | 0 | 0 | 4 | 0 | 0 | |||||||
| 19 | 5 | 0 | 0 | 5 | 0 | 0 | |||||||
| 20 | 6 | 0 | 0 | 6 | 0 | 0 | |||||||
| 21 | 7 | 0 | 0 | 7 | 0 | 0 | |||||||
| 22 | 8 | 0 | 0 | 8 | 0 | 0 |
<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
