Results 1 to 7 of 7

Time Difference Between History Events

Threaded View

  1. #1
    Registered User
    Join Date
    06-08-2012
    Location
    Indiana
    MS-Off Ver
    Excel 2007
    Posts
    4

    Time Difference Between History Events

    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.
    Last edited by JasmineL; 10-01-2014 at 03:42 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. A brief history of Excel Time!
    By craigproudfoot in forum Excel General
    Replies: 5
    Last Post: 02-18-2014, 11:04 AM
  2. Excel: Undo history clear after macro events
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-14-2013, 05:05 AM
  3. Need Help Creating Macro for Time Difference Between Two Events
    By Mike Bartlett in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2013, 05:14 PM
  4. Difference Between Specific Events
    By Tirren in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-19-2008, 05:52 PM
  5. Running macros/events clears undo history and clipboard
    By Creisti86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2007, 10:33 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1