Time duration calculation

[excel4jms]

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.

	A	B	C	D	E
1	Record ID <colgroup><col width="64"></colgroup><tbody> </tbody> <colgroup><col width="64"></colgroup><tbody> </tbody>	Time (hh:mm)	Duration (hh:mm) <colgroup><col width="97"></colgroup><tbody> </tbody>	Source <colgroup><col width="96"></colgroup><tbody> </tbody>	Service <colgroup><col width="68"></colgroup><tbody> </tbody>
2	4121965 <colgroup><col width="64"></colgroup><tbody> </tbody>	00:00 <colgroup><col width="85"></colgroup><tbody> </tbody>		INFORMATIONAL <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC <colgroup><col width="68"></colgroup><tbody> </tbody>
3	4121986	1:15 <colgroup><col width="85"></colgroup><tbody> </tbody>		BEGIN <colgroup><col width="96"></colgroup><tbody> </tbody>	Service 123 <colgroup><col width="68"></colgroup><tbody> </tbody>
4	4121987 <colgroup><col width="64"></colgroup><tbody> </tbody>	0:13 <colgroup><col width="85"></colgroup><tbody> </tbody>		BEGIN <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC
5	4121986 <colgroup><col width="64"></colgroup><tbody> </tbody>	4:20 <colgroup><col width="85"></colgroup><tbody> </tbody>		UPDATE <colgroup><col width="96"></colgroup><tbody> </tbody>	Service 123 <colgroup><col width="68"></colgroup><tbody> </tbody>
6	4121987 <colgroup><col width="64"></colgroup><tbody> </tbody>	4:30 <colgroup><col width="85"></colgroup><tbody> </tbody>		UPDATE <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC
7	4121986 <colgroup><col width="64"></colgroup><tbody> </tbody>	4:30 <colgroup><col width="85"></colgroup><tbody> </tbody>	3:15 <colgroup><col width="97"></colgroup><tbody> </tbody>	END <colgroup><col width="96"></colgroup><tbody> </tbody>	Service 123 <colgroup><col width="68"></colgroup><tbody> </tbody>
8	4121987 <colgroup><col width="64"></colgroup><tbody> </tbody>	01:15 <colgroup><col width="85"></colgroup><tbody> </tbody>		UPDATE <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC <colgroup><col width="68"></colgroup><tbody> </tbody>
9	4121987 <colgroup><col width="64"></colgroup><tbody> </tbody>	01:20 <colgroup><col width="85"></colgroup><tbody> </tbody>		INFORMATIONAL <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC <colgroup><col width="68"></colgroup><tbody> </tbody>
10	4121987 <colgroup><col width="64"></colgroup><tbody> </tbody>	2:20 <colgroup><col width="85"></colgroup><tbody> </tbody>	2:07 <colgroup><col width="97"></colgroup><tbody> </tbody>	END <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC <colgroup><col width="68"></colgroup><tbody> </tbody>
11	4121965 <colgroup><col width="64"></colgroup><tbody> </tbody>	01:00 <colgroup><col width="85"></colgroup><tbody> </tbody>		INFORMATIONAL <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC <colgroup><col width="68"></colgroup><tbody> </tbody>
12	4121987 <colgroup><col width="64"></colgroup><tbody> </tbody>	01:20 <colgroup><col width="85"></colgroup><tbody> </tbody>		UPDATE <colgroup><col width="96"></colgroup><tbody> </tbody>	Service ABC <colgroup><col width="68"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[rizwan_planning]

Cant you use pivot table

 

[DHayes]

Hi,
Copy this formula into C2 and drag down.

=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))),"")

 

[excel4jms]

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")