In yesterdays podcast, I proposed a formula to solve Jonathans dilemma of analyzing information by hourly buckets. An easier solution is to use the Group by Hour feature in a pivot table. Episode 495 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel podcast.
I am Bill Jelen.
Now, in yesterday's podcast, we had a call from Jonathan, who wanted to know how he could group all of his dates up to hourly information.
He had a column of dates here with both the date and the time.
He wanted to break that down into hourly buckets.
And I went through this big long formula.
Break out just the hour.
And I started to think about it after we posted the podcast, there's probably a much better way to do that.
I'm gonna do it using a pivot table.
So, I'm going to look here and notice that we have Alpha columns either in column A or B.
Either the T or the status column, We're going to use those to get a COUNT.
I'll select one cell in the data.
Use Data>Pivot table, And Click Finish.
I'm going to take the date and the time and drop it over here and call them A.
And then take one of my alphabetic columns and drop it where it says drop Data items.
Here that gives me a count of those Columns and right now We're seeing every single minute of every single day, which is going to be a really annoying report.
But here's the trick.
Here's the thing that's going to make it work.
I'm going to choose the first date and time.
Right click and say Group and Show Detail group and say that I want to group that just by hours.
Not five minutes, not by months quarters just choose Hours.
Click OK.
And very quickly Excel will take all of that data and consolidate it down to one row per Hourly bucket.
Perfect solution!
Much easier than the formula that we used yesterday, So, thanks to Jonathan for sending into question.
Sorry had to wait for the right answer, the next day.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Now, in yesterday's podcast, we had a call from Jonathan, who wanted to know how he could group all of his dates up to hourly information.
He had a column of dates here with both the date and the time.
He wanted to break that down into hourly buckets.
And I went through this big long formula.
Break out just the hour.
And I started to think about it after we posted the podcast, there's probably a much better way to do that.
I'm gonna do it using a pivot table.
So, I'm going to look here and notice that we have Alpha columns either in column A or B.
Either the T or the status column, We're going to use those to get a COUNT.
I'll select one cell in the data.
Use Data>Pivot table, And Click Finish.
I'm going to take the date and the time and drop it over here and call them A.
And then take one of my alphabetic columns and drop it where it says drop Data items.
Here that gives me a count of those Columns and right now We're seeing every single minute of every single day, which is going to be a really annoying report.
But here's the trick.
Here's the thing that's going to make it work.
I'm going to choose the first date and time.
Right click and say Group and Show Detail group and say that I want to group that just by hours.
Not five minutes, not by months quarters just choose Hours.
Click OK.
And very quickly Excel will take all of that data and consolidate it down to one row per Hourly bucket.
Perfect solution!
Much easier than the formula that we used yesterday, So, thanks to Jonathan for sending into question.
Sorry had to wait for the right answer, the next day.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
