Combine transaction data by time

[rfinnegan]

Hi All:

I have the unfortunate task of grouping several thousand transactions into 15 minute increments. The time stamp of the transaction is in column E and is formatted like this: 12/4/2018 9:08:23 AM

What I need to do is provide a count of the number of transactions that occurred from 9:00:00 AM - 9:14:59 AM, then the count that occurred from 9:15:00 AM - 9:29:59, etc.

I was hoping that the Subtotal function would have a feature I could use, but no luck. The good news/bad news is that each file only contains a single day's worth of transactions. So if needed I could put the time portion into a separate column (good news), the bad news is I have to do this for each and every day of 2018.

I also need to count these transactions by store. The store number is in column B ans the file is sorted by store number first, then by time.


Any help is appreciated.

 

[bbotzong]

If you can add a column to compute the portion of the hour, that's what I'd do. A formula can look at the minute in your time column, apply some if/then logic inside the TIME() function to create a time you can group by. If the time is in I2, the formula in J2 to compute the time is:

=TIME(HOUR(I2),IF(AND(MINUTE(I2)>=0,MINUTE(I2)<15),0,IF(AND(MINUTE(I2)>=15,MINUTE(I2)<30),15,IF(AND(MINUTE(I2)>=30,MINUTE(I2)<45),30,45))),0)

The time function takes the arguments (hour, minute, second). The hour is the first part, hour(i2). The minute is a three-part if function with a few AND() functions thrown in. If the minute >=0 and the minute < 15, then use :00 for minute. If the minute is >=30 and <45, then use :30. The last 0 is the seconds. This will give you a time-group column that you can use to subtotal that looks like this:

Time Time Group
9:00:15 AM 9:00:00 AM
9:07:56 AM 9:00:00 AM
9:11:26 AM 9:00:00 AM
9:18:02 AM 9:15:00 AM
9:23:02 AM 9:15:00 AM
9:29:02 AM 9:15:00 AM
9:32:12 AM 9:30:00 AM
9:41:38 AM 9:30:00 AM
9:44:08 AM 9:30:00 AM
9:51:56 AM 9:45:00 AM
9:55:16 AM 9:45:00 AM
9:59:02 AM 9:45:00 AM

 

[rfinnegan]

Thanks. I ended up using the FLOOR function. It worked to get everything into 15 minute buckets. Then I used a pivot table for the rest.