How to group times together into different periods in Excel?

Question

I've got a spreadsheet with a few thousand tweets in it, each one is time-stamped like so: 18/05/2014 21:14

Amongst other functions I've sorted out, I'd like to plot all the tweets on a graph grouped together by say 15-minute segments.

How can I group those different times(tweets) together?

Solution

I found a solution to this on another forum:

http://www.mrexcel.com/forum/excel-questions/359025-pivot-table-grouping-5-min-intervals.html

See the final answer.

It suggests adding a column to your raw data that contains a formula to round each time down to the previous increment.

For you, the formula would be as follows, where A1 contains the time stamp.

=TIME(HOUR(A1),FLOOR(MINUTE(A1),15),0)

In your example, 21:14 would become 21:00. 21:16, meanwhile, would become 21:15.

If you format your data as a table, the formula will auto-fill for all of the tweets.

Then, you can use a pivot chart based on the new column to create your graph. Since everything will be rounded down, the label 21:00 would include all tweets posted from 21:00 to 21:15.