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.
<tbody>
</tbody>
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.
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 | 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 |
<tbody>
</tbody>
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.
