Count values in 1 column, based on time values in another column

**JuJuBe** · 06-09-2010, 07:41 PM

I have a spreadsheet with 4 columns.

Column A is 'Invoice #'.

Column B is 'Date and Time Shipped'

Column C is the 'Day of the Month' 01-31.

Column D is a 3 digit 'Customer #' (AAA,BBB,CCC,111,222,etc..)

I need to count the number of orders, per customer, per day. This would be easy enough in a pivot table putting customer in the rows, day of month in the columns, and count of invoice # in the values but some invoices that are to the same customer #, within a small amount of time, (1 hour), of each other, should actually be counted as 1 transaction. See below

10:12
10:23
11:16
13:06
15:16
15:23

Assuming the times above are all for the same customer I should count 4 transactions. The 10:12 and 10:23 should be counted as 1, the 15:16 and 15:23 should be counted as 1, and all others should be counted as 1 by themselves. The hard part is establishing a base using the earliest time when 2 or more time values are within an hour, then re-establishing a base when a value falls outside of the 1 hour parameter. The 11:16 value is within 1 hour of the 10:23 but it should only be evaluated against 10:12, therefore not qualifying to be combined and needing to be counted on its own.

I'm stumped, thanks in advance!!!

**NBVC** · 06-10-2010, 10:07 AM

Possibly you could helper column that ups the hour of each cell to the 59th minute...

=INT(B2)+TIME(HOUR(B2),CEILING(MINUTE(B2),59),0)

and copy down... so the hour is not touched and you can still filter with it.....

**JuJuBe** · 06-10-2010, 11:48 AM

That takes times that are more than 1 hour apart and reduces them to 1 hour apart, leading us to believe they qualify as 1 transaction. (i.e. 8:03 and 9:37 become 8:59 and 9:59).

Am I missing something?

**NBVC** · 06-10-2010, 11:54 AM

I guess I assumed you were only interested in the hour portion... so 8 is different than 9..... and all 8's would become 8:59 and all 9's would be 9:59... making them unique....