Time differences

cashew1970

Board Regular
Joined
Feb 28, 2005
Messages
57
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
 
Upvote 0

Forum statistics

Threads
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?

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