How do I group times into 15 minute increments?

[mwf3]

I looked around but could not find a solution for this. Any help would be appreciated!!

I have a worksheet that has the following:

DATE TIME QTY
7/4/2005 8:00 1
7/4/2005 8:34 1
7/4/2005 9:01 3
7/4/2005 9:06 6
7/5/2005 10:16 7
7/5/2005 10:30 8
7/5/2005 10:50 6
7/5/2005 11:00 6
7/5/2005 11:06 3

I need to total QTY by Date and by 15 Minute Increment as shown:

DATE TIME QTY
7/4/2005 8:00 1
7/4/2005 8:30 1
7/4/2005 9:00 9
7/5/2005 10:15 7
7/5/2005 10:30 8
7/5/2005 10:45 6
7/5/2005 11:00 9

So, if the original time was 8:34 it should be regrouped to 8:30 since it did not reach the next 15 minute increment of 8:15.

thanks!!
Mark

 

[tactps]

You could use formulas to determine the right 15 minute interval:
=FLOOR(A1,TIME(0,15,0))
and then pivot table, or do sumif formulas.

 

[Brian from Maui]

Try,

=FLOOR(A1,1/96)

Where A1 houses 8:34. The next 15 minute increment should be 8:45 shouldn't it? Instead of 8:15.

 

[mwf3]

Hi Brian from Maui,


You are correct...it was late.................

 

[mwf3]

Both Formulas worked gret!!!! Thanks guys!!!