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

K

Kukku

New Member
Joined
Feb 23, 2019
Messages
1
ABCDEFGHIJKLM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]SN.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"][/TD]
[TD="align: center"]DT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Actual DT[/TD]
[TD="align: center"]Overlap with (for representation only)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SN.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"][/TD]
[TD="align: center"]DT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Actual DT[/TD]
[TD="align: center"]Overlap with (for representation only)[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]Start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]End[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]Start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]End[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]11-02-2019 16:50:00[/TD]
[TD="align: center"]12-02-2019 02:30:00[/TD]
[TD="align: center"]580[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.5 in A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]11-02-2019 16:50:00[/TD]
[TD="align: center"]12-02-2019 19:27:00[/TD]
[TD="align: center"]1597[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.5 in A[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]11-02-2019 14:50:00[/TD]
[TD="align: center"]11-02-2019 15:20:00[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.5 in A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]11-02-2019 13:10:00[/TD]
[TD="align: center"]11-02-2019 14:30:00[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.5 in A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]11-02-2019 11:18:00[/TD]
[TD="align: center"]12-02-2019 09:00:00[/TD]
[TD="align: center"]1302[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.5 in A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]11-02-2019 11:17:00[/TD]
[TD="align: center"]12-02-2019 19:27:00[/TD]
[TD="align: center"]1930[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]1930[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: center"]11-02-2019 11:15:00[/TD]
[TD="align: center"]12-02-2019 11:15:00[/TD]
[TD="align: center"]1440[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.7 in A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: center"]11-02-2019 09:50:00[/TD]
[TD="align: center"]11-02-2019 16:50:00[/TD]
[TD="align: center"]420[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]87[/TD]
[TD="align: center"]after overlap with SN.5 in A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]SN.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"][/TD]
[TD="align: center"]DT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Actual DT[/TD]
[TD="align: center"]Overlap with (for representation only)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SN.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"][/TD]
[TD="align: center"]DT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Actual DT[/TD]
[TD="align: center"]Overlap with (for representation only)[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]Start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]End[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]Start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: center"]End[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]11-02-2019 14:50:00[/TD]
[TD="align: center"]11-02-2019 15:50:00[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.5 in A[/TD]
[TD="align: center"][/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]11-02-2019 11:20:00[/TD]
[TD="align: center"]11-02-2019 15:30:00[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]overlap with SN.5 in A[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]11-02-2019 08:30:00[/TD]
[TD="align: center"]11-02-2019 08:45:00[/TD]
[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]15[/TD]
[TD="align: center"]no overlap[/TD]
[TD="align: center"][/TD]
[TD="align: right"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]

</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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You must log in or register to reply here.

Similar threads

K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
586
awoohaw
awoohaw
J
  • Question Question
Sum only when time overlaps
Replies
1
Views
274
awoohaw
awoohaw
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
790
felixstraube
F
A
  • Solved
compare brands between two sheets and show minus and positive values based on date (today)
Replies
6
Views
235
abdo meghari
A
A
  • Question Question
Time overlap and reduced the overlap time
Replies
1
Views
517
Anthony47
A

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top