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

1. ## 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.

2. ## 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.

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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. ## 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.

9. ## 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. ## 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. ## Re: Task: to get one record per day from dates often separated by seconds

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

#### 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