Time differences

[cashew1970]

Hi All.

I have some data where i need to know the time beteween the activies for each occurance.

In Col A, we have the Unique Incident Ref
In Col B, the date and time that the Activity happened
Col C is the Status of the incident at that point in time
Col D was a running count of the steps in the Incident.

Ideally what I am looking to do is calculate the total amount of time that the incident was in each of its status.

I guess that if I can work out the diff between the lines, I will then be able to total based on Status???

Book1
	A	B	C	D	E
1	IncidentId	EventDate	LongValue	running-count(LongValue
2	305015	09/04/200316:46	Active	1	if
3	305015	10/04/200310:54	With3rdLineTeam	2
4	305015	14/04/200311:05	With3rdLineTeam	3
5	305015	15/04/200310:35	With3rdLineTeam	4
6	305015	15/04/200310:46	With3rdLineTeam	5
7	305015	15/04/200312:52	Respond-ClockRunning	6
8	305015	15/04/200313:04	AwaitingCustVerification	7
9	305015	16/04/200318:23	AwaitingCustVerification	8
10	305015	22/04/200309:44	Closed	9
11	305014	09/04/200316:44	Active	1
12	305014	09/04/200316:44	Active	2
13	305014	09/04/200317:11	Active	3
14	305014	14/04/200311:20	Active	4
15	305014	15/05/200309:27	Active	5
16	305014	27/05/200310:33	Active	6
17	305014	30/07/200316:53	Active	7
18	305014	27/08/200309:54	Active	8
19	305014	16/09/200314:46	Active	9
20	305014	06/10/200312:26	Active	10
21	305014	21/10/200315:54	Active	11
22	305014	08/01/200409:59	Active	12
Data

 

[fairwinds]

Hi,

Try:

=IF(ISNUMBER(MATCH(1,(A2:$A$30=A2)*(C2:$C$30<>C2),0))*(C2<>C1),INDEX(B2:$B$30,MATCH(1,(A2:$A$30=A2)*(C2:$C$30<>C2),0))-B2,"")

confirmed with Ctrl + shift + enter in E2, then dragged down.

formula cells should be custom formatted as [h]:mm

Book1
	A	B	C	D	E
1	IncidentId	EventDate	LongValue	running-count(LongValue
2	305015	2003-04-09 16:46	Active	1	18:08
3	305015	2003-04-10 10:54	With 3rd Line Team	2	121:58
4	305015	2003-04-14 11:05	With 3rd Line Team	3
5	305015	2003-04-15 10:35	With 3rd Line Team	4
6	305015	2003-04-15 10:46	With 3rd Line Team	5
7	305015	2003-04-15 12:52	Respond-ClockRunning	6	0:12
8	305015	2003-04-15 13:04	Awaiting Cust Verification	7	164:40
9	305015	2003-04-16 18:23	Awaiting Cust Verification	8
10	305015	2003-04-22 09:44	Closed	9
11	305014	2003-04-09 16:44	Active	1
12	305014	2003-04-09 16:44	Active	2
13	305014	2003-04-09 17:11	Active	3
14	305014	2003-04-14 11:20	Active	4
Sheet3

 

[cashew1970]

Thanks for that... I will give it a go