grouping counts by date and time

edestefano

New Member
Joined
Nov 17, 2010
Messages
1
Barry Houdini provided the following which helped divide my data set into 15 min chunks of time and list the number of counts in each time period:

=SUMPRODUCT((FLOOR(MOD(B$1:B$4,1),"0:15")=C1)+0

where B is time of an event in hours:minutes.

This worked well on a small data set using 15 minute time periods. However, my data set is large (90 days) and I want to divide it into 5 hour time periods. I think I'm having problems incorporating the date in the above formula. I tried to make column B include date AND time, but I'm not sure how to define the significance variable for the FLOOR.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Threads
1,108,763
Messages
5,524,756
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top