+ Reply to Thread
Results 1 to 11 of 11

Task: to get one record per day from dates often separated by seconds

  1. #1
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Task: to get one record per day from dates often separated by seconds

    I’ve got a pile of data (450k records or so) on the quit date people set when trying to give up smoking. Much of this is noise, as an artefact with the way data was collected meant many people recorded quit dates just a few seconds apart.

    My goal is to have only one record per day. For example, if someone has 15 different quit dates spread over five days. I’d like to end up with one quit date for each of these five days and for that quit date to be the one last created that day. To be clear, I need three pieces of information for each of these days: author; date/time record was created and the quit date itself. Please see example1.xlsx for sample records.

    I would then like to work out the difference in days between those quit dates. In the case of someone with five quit dates across five days I’d like to know how far the first is from the second, the second from the third, and so on. Obviously the challenge here is that some people will have one quit date, others will have 2, 3, 5, 10 and so on.
    Attached Files Attached Files

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Task: to get one record per day from dates often separated by seconds

    This should give you what you want. I sorted the data by the first column and the 3rd column.

    Blank cells indicate that there wasn't an interval or that the person in column A is different than the one before. If someone only appears once, there will not be an interval to calculate and that is blank.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Task: to get one record per day from dates often separated by seconds

    That sorts out the interval problem nicely, thank you.

    Do you have any idea how I could only have one record per day for each 'author'? For example, in the sample data author IpqYReOQ0I has 20 records covering three days. I'd like to end up with them having three records, one for each day, with that record being the most recent entry in the Record Created column for that day.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Task: to get one record per day from dates often separated by seconds

    This Pivot Table summary should give you pretty close to what you want.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Task: to get one record per day from dates often separated by seconds

    It does. I'm not great with Pivot Tables, but is it possible to get:
    1) All blanks in the Author column filled with the Author name above?
    2) The date record created field completed with the date that relates to the quit date next to it?

    I need to do a few other calculations with this data you see.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Task: to get one record per day from dates often separated by seconds

    Isn't what you are asking what you already have in the first sheet?

    Without knowing where you are going with this, I can't possibly answer your question. It appears that you are going from one step to another to another and they may not be helpful in solving the actual problem.

    What is the ultimate outcome of these calculations? You have given the start, what is the finished product going to be?

  7. #7
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Task: to get one record per day from dates often separated by seconds

    What I'd like to end up with is one record per author per day and for that record to contain: Author; Date Record Created; Quit Date

    What I'd like different from Example1 V2 is: Cells A5 - A7 filled with iPpnNXZcrx; Cell A9 filled with: IPq3IrxYKH; cell A11 - A13 filled with IPQsNb4T6s - and so on down that column.

    In addition, this worksheet only contains one entry for Date Record Created per author. What's needed is to have the date the record was created for all an author's quit dates. Ie cell B5 with the quit date for 24th April, cell B6 with the quit date for 25th April, cell B7 with the quit date for 26th April and so on down that column.

    I'm trying to determine a few different things with this data, most of which I think I can work out if I've got one record per day for each author that contains Author, Date Record Created and Quit Date. FYI I want to work out the difference between the date the record was created and the date the quit date was set for (I'm trying to determine how many quit dates are spontaneous and how many are planned); working out whether a quit date is more likely to be set on a Monday than a Friday; seeing whether people set quit dates close to - or far apart from each other (the latter might indicate the quit attempt failed, but they liked the app I've created and so came back to it for a second quit attempt) and a few other things besides.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Task: to get one record per day from dates often separated by seconds

    There appears to be a problem with your data. Many of the Quit Dates are BEFORE the Date Record Created.....impossible??? Calculations with Date Record Created and Date Quit will not work with Quit dates before date of creation.....is there something that I am missing here?

    Pivot tables cannot produce the layout that you want so I deleted it.

    To work out Spontaneous vs Planned quit, you must have a time frame in mind. This has to be a positive number between the Date Record Created and the Quit Date as Excel does not work with negative time. So, the Quit Date must be later than the Date Record Created. All the records highlighted in Red do not meet this criteria. There is something wrong with this data. If the creation date was entered after the Quit Date then that data is meaningless.

    Maybe advanced filtering will be able to produce what you want. Right now, I'm not sure about anything.
    Last edited by newdoverman; 12-08-2013 at 10:35 AM.

  9. #9
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Task: to get one record per day from dates often separated by seconds

    Date Record Created means the date the Quit Date was created. This gives us two dates: The quit date itself, and the point in time in which that quit date was set.

    For example, iPpnNXZcrx made an entry at: 26 Apr 13 00:41 and set a quit date for: 24 Apr 13 00:39. Ie, on 26th April this person set their quit date for two days previously, the 24th April.

    Does that clarify things?

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Task: to get one record per day from dates often separated by seconds

    I can't make sense of this so I'm going to have to drop out of trying to solve your problem.

  11. #11
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Task: to get one record per day from dates often separated by seconds

    Understood, thanks for your efforts.

+ 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. need code to record values every 5 seconds
    By okcsteve in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2013, 12:44 PM
  2. Dates/Time separated. Help please.
    By auswtz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-31-2013, 08:03 AM
  3. Record an ever changing cells value from Sheet 1 every 30 seconds into Sheet 2
    By stejjfc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-20-2013, 07:03 PM
  4. [SOLVED] Request a Macro to Convert data into Dates (Multiple Dates Values separated by Line)
    By seenai in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2012, 02:28 AM
  5. Replies: 4
    Last Post: 01-18-2010, 04:29 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