Over lapping time calculation for same entries

M

meowbhow

New Member
Joined
May 31, 2018
Messages
21
I need the overlapping time in column D, for example, A2, A7 and A8 has keyword KUND (USF). If there is any common time between these three entries then we should mention it in column D

[TABLE="class: cms_table, width: 527"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlapping Time[/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:27:22 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Janah (USF) Khuzdar[/TD]
[TD="align: right"]7:27:23 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD="align: right"]7:27:24 PM[/TD]
[TD="align: right"]9:21:24 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD="align: right"]7:00:25 PM[/TD]
[TD="align: right"]10:12:25 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD="align: right"]7:27:50 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:27:57 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:28:03 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD="align: right"]7:28:06 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
This might help. If your Start Time and End Time can be listed as full Date/Time serial numbers, this task is easy; otherwise it's a conceptual nightmare. These Date/Times are custom-formatted in my Excel worksheet as mm/dd/yyyy hh:mm:ss AM/PM

Copy I2 downwards.


Book1
FGHI
1NameStart TimeEnd TimeOverlapping Time
2Kund (USF) Khuzdar06/02/2018 07:27:22 PM06/02/2018 08:27:22 PMTRUE
3Janah (USF) Khuzdar06/02/2018 07:27:23 PM06/02/2018 09:27:22 PMFALSE
4Hitachi (USF) Khuzdar06/02/2018 07:27:24 PM06/02/2018 09:21:24 PMTRUE
5Herok (USF) Khuzdar06/02/2018 07:00:25 PM06/02/2018 10:12:25 PMTRUE
6Hitachi (USF) Khuzdar06/02/2018 07:27:50 PM06/02/2018 08:27:22 PMTRUE
7Kund (USF) Khuzdar06/02/2018 07:27:57 PM06/02/2018 09:27:22 PMTRUE
8Kund (USF) Khuzdar06/02/2018 07:28:03 PM06/02/2018 09:27:22 PMTRUE
9Herok (USF) Khuzdar06/02/2018 07:28:06 PM06/02/2018 08:27:22 PMTRUE
Sheet1
Cell Formulas
RangeFormula
I2=SUMPRODUCT((A2=$A$2:$A$9)*(G2<$H$2:$H$9)*(H2>=$G$2:$G$9))>1
 
Upvote 0
Thank you brother for your efforts, please do something and just tell me the OVERLAPPING TIME in minutes or hours. Its really important for me since my JOB is dependent on it. Kindly put some more efforts, and i will do anything for you if i get the desired result. Thank you !! :cool:
 
Upvote 0
You're welcome.

What exactly do you mean overlapping time in minutes or hours? There can be several of the items in the Name column that overlap with one another (as there are in your data for Kund), which makes that kind of thing nearly impossible.
 
Last edited:
Upvote 0
See if this is what you need


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Name​
[/TD]
[TD]
Start Time​
[/TD]
[TD]
End Time​
[/TD]
[TD]
Overlapping Time​
[/TD]
[TD][/TD]
[TD]
Criteria​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Kund (USF) Khuzdar​
[/TD]
[TD]
7:27:22 PM​
[/TD]
[TD]
8:27:22 PM​
[/TD]
[TD]
01:58:44​
[/TD]
[TD][/TD]
[TD]
Kund (USF) Khuzdar​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Janah (USF) Khuzdar​
[/TD]
[TD]
7:27:23 PM​
[/TD]
[TD]
9:27:22 PM​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Hitachi (USF) Khuzdar​
[/TD]
[TD]
7:27:24 PM​
[/TD]
[TD]
9:21:24 PM​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Herok (USF) Khuzdar​
[/TD]
[TD]
7:00:25 PM​
[/TD]
[TD]
10:12:25 PM​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Hitachi (USF) Khuzdar​
[/TD]
[TD]
7:27:50 PM​
[/TD]
[TD]
8:27:22 PM​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Kund (USF) Khuzdar​
[/TD]
[TD]
7:27:57 PM​
[/TD]
[TD]
9:27:22 PM​
[/TD]
[TD]
01:59:19​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Kund (USF) Khuzdar​
[/TD]
[TD]
7:28:03 PM​
[/TD]
[TD]
9:27:22 PM​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Herok (USF) Khuzdar​
[/TD]
[TD]
7:28:06 PM​
[/TD]
[TD]
8:27:22 PM​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Total​
[/TD]
[TD]
03:58:03​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Criteria in F2

Array formula in D2 copied down
=IF(A2=F$2,SUM(IF(A$2:A$9=A2,IF(1-((B2>C$2:C$9)+(B$2:B$9>C2)),IF(ROW(A$2:A$9)>ROW(A2),IF(C$2:C$9>C2,C2,C$2:C$9)-IF(B$2:B$9>B2,B$2:B$9,B2))))),"")
Ctrl+Shift+Enter

Formula in D10
=SUM(D2:D9)

Format D2:D10 as hh:mm:ss

Observe that
The result in D2 01:58:44 is the sum of
00:59:25 (overlap of row 2 with row 7) + 00:59:19 (overlap of row 2 with row 8) = 01:58:44

The result in D7 is the overlap of row 7 with row 8 = 01:59:19

D10 is the total: 03:58:03, that is 3 hours 58 minutes and 3 seconds

Hope this helps

M.
 
Last edited:
Upvote 0
I need exactly this, Please tell me why my answer is coming zero ?
[TABLE="width: 606"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name [/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlapping Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:27:22 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD="align: right"]0:00:00[/TD]
[TD][/TD]
[TD]Kund (USF) Khuzdar[/TD]
[/TR]
[TR]
[TD]Janah (USF) Khuzdar[/TD]
[TD="align: right"]7:27:23 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD="align: right"]7:27:24 PM[/TD]
[TD="align: right"]9:21:24 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD="align: right"]7:00:25 PM[/TD]
[TD="align: right"]10:12:25 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD="align: right"]7:27:50 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:27:57 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD="align: right"]0:00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:28:03 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD="align: right"]0:00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD="align: right"]7:28:06 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
meowbhow said:
Its really important for me since my JOB is dependent on it. Kindly put some more efforts, ...
Click to expand...
If it is that important perhaps you should consider paying somebody to help you rather than trying to force volunteer helpers in a free public forum like this. Also refer to 5g and 5h of the Forum Use Guidelines (& #5 of the Forum Rules)
 
Upvote 0
My sincere apologies in this regards, i am just 23 years old and its my first JOB and my first assignment. I was panicked, i am Sorry !! I will surely learn the ethics of Volunteer ethics Sir. Thank you for your kind guidance
 
Upvote 0
Dear Brother Marcelo, i think you have pasted wrong formula. Since when i evaluated the formula the result was like this, " IF(TRUE,Sum(IF(TRUE,FALSE))),''). Please check the formula again, looking forward for your usual kind support. Thank you !!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Lacan
  • Solved
Let formula?
2
Replies
12
Views
464
Lacan
Lacan
S
  • Solved
Average in excel
Replies
3
Views
257
etaf
etaf
N
  • Question Question
Advisor login, logout & break calculation
Replies
1
Views
255
Cubist
Cubist
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
598
Alex Blakenburg
A
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
400
freeb1893
F

Forum statistics

Threads
1,224,885
Messages
6,181,588
Members
453,055
Latest member
cope7895

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