How to group times together into different periods in Excel?
date, excel, pivot, twitter
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.
Problem
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?