+ Reply to Thread
Results 1 to 4 of 4

Help with formula to determine latest timestamp for a variety actions per user

  1. #1
    Registered User
    Join Date
    09-22-2014
    Location
    Boston
    MS-Off Ver
    Version 14.2.3
    Posts
    2

    Help with formula to determine latest timestamp for a variety actions per user

    Hi there -

    I'm struggling with pulling some data for excel and I think that I need an formula, but I'm not sure. Any help would be VERY appreciated!

    I've got a list of data that indicates the date and time that about 2,500 people completed up to 8 separate actions. The data is in the format: User ID #, Date completed (xx/xx/xxx xx:xx), and Action ID. I need to pull the date and time when they completed their last action. The issue I'm running into is that these actions don't have to be in a particular order, so they could have completed action #5 before action #8, thus action # 5 would have a later 'date completed' time stamp. And this is the timestamp I want.

    I'm not able to do this in a pivot table since it converts the date to another format. Anyone have any ideas of a formula that I can use to complete this? Or if there is a way that I can sort this data via pivot or sort?

    Any help would be VERY MUCH appreciated.

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Help with formula to determine latest timestamp for a variety actions per user

    Quote Originally Posted by katecbernier View Post
    I'm not able to do this in a pivot table since it converts the date to another format.
    You can change the format of PivotTable cells just like any other cells.

    Or

    Select record in target range
    "PivotTable Tools: Options ribbon" => "Active Field" panel => "Field Settings" button => select "Number Format" in the Value Field Settings dialogue popup box.
    You can also open the dialogue box from the menu that you can click open when a field is in one of the area boxes.

    Also you can select "MAX" instead of "SUM" in that popup; drop "USER ID" in the row label, timestamp in the Values, change the settings of Values for "MAX" and desired format, and you're golden, probably.

  3. #3
    Registered User
    Join Date
    09-22-2014
    Location
    Boston
    MS-Off Ver
    Version 14.2.3
    Posts
    2

    Re: Help with formula to determine latest timestamp for a variety actions per user

    Hi Ben -

    Thanks SO much for your reply! Here is a screenshot of the data I'm working with:

    Screen Shot 2014-09-22 at 5.32.13 PM.png

    It looks like your last solution will work the best ("Also you can select "MAX" instead of "SUM" in that popup; drop "USER ID" in the row label, timestamp in the Values, change the settings of Values for "MAX" and desired format, and you're golden, probably"), but I have to keep User ID, since I need to pull the last completed action on a per user basis. So once I follow your instructions, I get stuck here:

    Screen Shot 2014-09-22 at 5.43.47 PM.png

    Is there any way I can pull just the lastest date for each particular user?
    Attached Images Attached Images

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Help with formula to determine latest timestamp for a variety actions per user

    Can you post the spreadsheet itself instead of sceencaps of it? This forum is set to accept documents like XLSX as attachments to posts; pushing the spreadsheet around directly is strongly preferred to just pictures of them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 11-05-2011, 04:18 PM
  2. Logon Box - User / Pass - Unique actions per user.
    By CVinje in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-04-2009, 07:41 AM
  3. Open Latest TextFile with latest TimeStamp
    By pr4t3ek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2009, 10:03 AM
  4. Actions between user actions
    By Indiana Epilepsy and Child Neurology in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2006, 12:45 PM

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