Time differences

cashew1970

Board Regular
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
ABCDE
1IncidentIdEventDateLongValuerunning-count(LongValue
230501509/04/200316:46Active1if
330501510/04/200310:54With3rdLineTeam2
430501514/04/200311:05With3rdLineTeam3
530501515/04/200310:35With3rdLineTeam4
630501515/04/200310:46With3rdLineTeam5
730501515/04/200312:52Respond-ClockRunning6
830501515/04/200313:04AwaitingCustVerification7
930501516/04/200318:23AwaitingCustVerification8
1030501522/04/200309:44Closed9
1130501409/04/200316:44Active1
1230501409/04/200316:44Active2
1330501409/04/200317:11Active3
1430501414/04/200311:20Active4
1530501415/05/200309:27Active5
1630501427/05/200310:33Active6
1730501430/07/200316:53Active7
1830501427/08/200309:54Active8
1930501416/09/200314:46Active9
2030501406/10/200312:26Active10
2130501421/10/200315:54Active11
2230501408/01/200409:59Active12
Data

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
ABCDE
1IncidentIdEventDateLongValuerunning-count(LongValue
23050152003-04-09 16:46Active118:08
33050152003-04-10 10:54With 3rd Line Team2121:58
43050152003-04-14 11:05With 3rd Line Team3
53050152003-04-15 10:35With 3rd Line Team4
63050152003-04-15 10:46With 3rd Line Team5
73050152003-04-15 12:52Respond-ClockRunning60:12
83050152003-04-15 13:04Awaiting Cust Verification7164:40
93050152003-04-16 18:23Awaiting Cust Verification8
103050152003-04-22 09:44Closed9
113050142003-04-09 16:44Active1
123050142003-04-09 16:44Active2
133050142003-04-09 17:11Active3
143050142003-04-14 11:20Active4
Sheet3

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

Replies
5
Views
643
Replies
5
Views
457
Replies
19
Views
859
Replies
9
Views
517
Replies
1
Views
274

1,211,983
Messages
6,105,208
Members
447,955
Latest member
BWheezy

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?

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

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