Time Difference Between History Events

[JasmineL]

I've been tasked with a challenging request which I believe a macro will be required. Of course, my experience with macros is recording and modifying those created by others...sigh.

Here is a scenario:
I have an item with various history events. The item's unique ID is its Date/Time stamp. By using the Event Begin Date/Time, I need to find the difference in time when queues and statuses change. I have various combinations, but for this example, I'll focus on Admin. When the item is in the queue ADMIN (regardless of status, so the first event of queue Admin), I need to calculate the span of time between the Event Begin Date/Time for this event and when the item's status changes to a new status. So, in the table below, I need to identify the first event in which the item appears in queue Admin, capture the Event Begin Date/Time; identify the status of this event and determine when the status changes; capture the Event Begin Date/Time of the new status; and, find the span of time between these two date/times captured.

I've highlighted the two events the formula/macro should find in this example. The end result should be Event Begin Date/Time of Event 5 minus Event Begin Date/Time of Event 2.

Date/Time	Account #	Unit	Work Category	Event Begin Date/Time	Queue	Status	Event
2014-09-23-06.11.18.689600	7777777	Unit A	Mail	2014-09-23-06.11.18.693600	Matching	New	1
2014-09-23-06.11.18.689600	7777777	Unit A	Mail	2014-09-23-06.18.09.530580	Admin	Seek	2
2014-09-23-06.11.18.689600	7777777	Unit A	Mail	2014-09-24-02.38.55.851560	Admin	Seek	3
2014-09-23-06.11.18.689600	7777777	Unit A	Mail	2014-09-25-02.45.38.687560	Admin	Seek	4
2014-09-23-06.11.18.689600	7777777	Unit A	Mail	2014-09-25-12.07.08.445580	Worker	Draft	5
2014-09-23-06.11.18.689600	7777777	Unit A	Mail	2014-09-25-12.07.54.719580	Worker_Ind	Draft	6
2014-09-23-06.11.18.689600	7777777	Unit A	Mail	2014-09-29-14.13.23.061580	END	Letter	7

A slight variation of the above example is that if the status changes while still in queue ADMIN, I'd need to capture the difference of time between the initial appearance in the queue ADMIN and when the status changed. So, in the table above, if Event 4 Status was Found, the time difference calculation would be Event Begin Date/Time of Event 4 minus Event Begin Date/Time of Event 2.

Anybody have an initial ideas/thoughts/tools you are aware of that does this type of work?

Thanks.

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[TMS]

You can use MATCH to locate the first occurrence of Admin and, with that, use INDEX to return the date and time associated with that first occurrence. Then it's just a case of subtracting the earlier date/time from the current event's date/time.


Regards, TMS

[TMS]

Like ...

Please Login or Register  to view this content.

And, hence,

Please Login or Register  to view this content.

Regards, TMS

[JasmineL]

Here are a couple of examples and expected results

[Pepe Le Mokko]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...ry-events.html

[JasmineL]

Thank you for the feedback.