Time duration calculation

E

excel4jms

New Member
Joined
Nov 21, 2017
Messages
2
Any help on this would be MUCH appreciated!

What I am trying to accomplish is basically, determining the time duration between a start and end time value relevant to a given Record id and Source entry.

The formula would need to identify matching Record ID's, then determine if the Record ID is associated with a BEGIN or END Source so that the Duration between the two can be calculated.

There would also be circumstances when the BEGIN time would not be on the same sheet as the END time. It would be ideal if those particular entries could be flagged with something like "NO MATCH". This would tell the user that they would need to look at a previous sheet to get the BEGIN time info needed.

I have attached an example sheet to hopefully help with the explanation.

Thanks.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Record ID
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Time (hh:mm)
[/TD]
[TD][TABLE="width: 97"]
<colgroup><col width="97"></colgroup><tbody>[TR]
[TD="class: xl65, width: 97"]Duration (hh:mm)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]Source
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121965
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]INFORMATIONAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service ABC
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]4121986
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]1:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]BEGIN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service 123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121987[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]0:13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]BEGIN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Service ABC
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121986[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]4:20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]UPDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service 123
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121987[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]4:30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]UPDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Service ABC
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121986[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]4:30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 97"]
<colgroup><col width="97"></colgroup><tbody>[TR]
[TD="class: xl65, width: 97"]3:15
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]END[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service 123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121987[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]01:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]UPDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service ABC
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121987[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]01:20
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]INFORMATIONAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service ABC
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121987[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]2:20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 97"]
<colgroup><col width="97"></colgroup><tbody>[TR]
[TD="class: xl65, width: 97"]2:07
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]END[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service ABC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121965[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]01:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]INFORMATIONAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service ABC
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4121987[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="class: xl65, width: 85"]01:20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]UPDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col width="68"></colgroup><tbody>[TR]
[TD="class: xl65, width: 68"]Service ABC[/TD]
[/TR]
</tbody>[/TABLE]
[/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
Hi,
Copy this formula into C2 and drag down.
Code: 
=IF(D2="End",SUMPRODUCT((SUMIFS($B$2:$B$12,$D$2:$D$12,"End",$A$2:$A$12,A2))-(SUMIFS($B$2:$B$12,$D$2:$D$12,"Begin",$A$2:$A$12,A2))),"")
 
Last edited:
Upvote 0
DHayes, That does appear to get me closer to what I need but when I try and expand on your example using multiple "BEGIN's" that reference the same "END" I am encountering an issue where the duration calculation does not process.

Here is an example of what I have if you can offer some insight:

=IFERROR(
IF(D2="END_SYSTEM_DEGRADED",SUMPRODUCT((SUMIFS($B$2:$B$12,$D$2:$D$12,"END_SYSTEM_DEGRADED",$A$2:$A$12,A2))-(SUMIFS($B$2:$B$12,$D$2:$D$12,"BEGIN_SYSTEM_DEGRADED",$A$2:$A$12,A2))),
IF(D2="END_SYSTEM_DEGRADED",SUMPRODUCT((SUMIFS($B$2:$B$12,$D$2:$D$12,"END_SYSTEM_DEGRADED",$A$2:$A$12,A2))-(SUMIFS($B$2:$B$12,$D$2:$D$12,"BEGIN_EMERGENCY_RFC ",$A$2:$A$12,A2))),
"")),"NO MATCH")
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Inventory disposal plan
Replies
3
Views
295
Spaceballs_the_fanboy
S
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
79
6StringJazzer
6StringJazzer
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
697
AOB
AOB
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
O
  • Solved
Travel Duration allowing for more than one day and different time zones
Replies
7
Views
147
myall_blues
myall_blues

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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