Pivot Table - Grouping in 5 min Intervals

JB R

New Member
Joined
Dec 13, 2008
Messages
1
I have raw data showing activity throughout the day. Each entry is time stamped. I would like to create a pivot table and group it in 5 minute intervals throughout the day. This would create time blocks that i would include in a chart. I would prefer a solution that works with 2003.

Thanks in advance
:)
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Robert E Lee

Active Member
Joined
Aug 10, 2005
Messages
266
I would suggest another column next to the time stamp, the first row to be equal to the first row in the previous column, subsequent cells to contain this formula

=IF(J2<(I2+0.003472),I2,I2+0.003472)

this should give you a list of times at 5 minute intervals

Robert
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I agree in so far as a helper column is warranted though assuming you want to group on the basis of 00:00 to 00:04:59, 00:05:00 to 00:09:59 etc... I would opt for a simple:

J2: =TIME(HOUR(B7),FLOOR(MINUTE(B7),5),0)
Where B7 contains Time Stamp

This would round down the time stamp to the nearest 5 minute interval based on first interval being 00:00:00.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,951
Messages
5,525,833
Members
409,666
Latest member
aquabit

This Week's Hot Topics

Top