Episode #1381, Michael asks, "How can I group a Pivot Table up to Quarter Hours?" There is no ideal way to do this in the Pivot Table or in PowerPivot. Bill shows us how to add a Formula to the original Data Set to solve the problem. Learn Excel!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1381: Quarter Hours.
Hey welcome back to the MrExcel netcast, I'm Bill Jellen and today's question is sent in by Michael.
Michael has a large dataset of events that have happened and it's in date-time format.
He's trying to create a PivotTable from that and wants to group that up to quarter hours.
So Insert PivotTable and click OK.
We'll put the dates down on the left hand side, the count there--we want a group that field, okay so normally we come here to Group Field and you see it’s very frustrating.
If we would choose only days, they could say the number of days but that is the only one of these that works.
If we would choose for example, minutes, that's greyed out and you can't say roll it up to 15-minute increments or anything like that.
So there's really no good way to do this.
If we do hours and minutes, it's going to be every minute instead of quarter hours and of course hours will just give us hours.
So let's think about how Excel stores dates.
Excel stores dates as a fractional portion of a day, so noon is 0.5 and 6am is 0.25 and so on.
So if we had 24 hours in a day and we want to break it into quarter hours we can just do a simple little formula here.
See if there's 96 quarter hours in a day and since each day is equal to the number 1 so if we do 1 divided by 0.96 that is the answer, all right.
So that's how many portions a day would come down to quarter hours.
So let's see if we can build a little formula over here back in our original dataset.
We'll use that number and copy it over here and paste, all right.
So I'm going call this “QtrHour” and the formula is going be =MOD([ @When ],1) What's that going to do?
That's going take off any whole days and give me just the time.
All right.
I'm not going use round I'm going use something called MROUND.
I think that stands for like multiple round to a certain multiple and normally I would use this to round up to the nearest 5 or 10 or something like that.
But very interesting what I'm going to do is I'm going to use that number, 1 divided by 96.
The formula is now =MROUND(MOD([ @When ],1),(1/96)) Let's see if this actually worked perfectly for us.
I'm going to format that column Ctrl+1.
We will go into Time.
Actually let's do one that shows the seconds.
Click OK and you'll see that we've perfectly rounded everything now to the nearest quarter hour.
Now by the way Michael had asked, “hey, would PowerPivot do this?” While PowerPivot adds a lot of date and time intelligence and actually look it added a lot of date intelligence not a lot of time intelligence so you'd have to do the exact same thing in PowerPivot.
So PowerPivot could solve it but using this exact same method.
So let's just stay here.
We'll get rid of our other PivotTable.
I didn't need to do that I could have just refreshed but Insert PivotTable, click OK and now we can put quarter hour in row labels and count in values so we get to see how many items occurred in each quarter hour.
All right, so Michael I know you were looking for something better, something building the PivotTables and unfortunately it's just not there.
PowerPivot doesn't add it either but this formula here, =MROUND(MOD([ @When ],1),(1/96)) will get you back into the quarter hour Okay well hey I want to thank everyone for stopping by.
I want to thank Michael for sending that question in.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1381: Quarter Hours.
Hey welcome back to the MrExcel netcast, I'm Bill Jellen and today's question is sent in by Michael.
Michael has a large dataset of events that have happened and it's in date-time format.
He's trying to create a PivotTable from that and wants to group that up to quarter hours.
So Insert PivotTable and click OK.
We'll put the dates down on the left hand side, the count there--we want a group that field, okay so normally we come here to Group Field and you see it’s very frustrating.
If we would choose only days, they could say the number of days but that is the only one of these that works.
If we would choose for example, minutes, that's greyed out and you can't say roll it up to 15-minute increments or anything like that.
So there's really no good way to do this.
If we do hours and minutes, it's going to be every minute instead of quarter hours and of course hours will just give us hours.
So let's think about how Excel stores dates.
Excel stores dates as a fractional portion of a day, so noon is 0.5 and 6am is 0.25 and so on.
So if we had 24 hours in a day and we want to break it into quarter hours we can just do a simple little formula here.
See if there's 96 quarter hours in a day and since each day is equal to the number 1 so if we do 1 divided by 0.96 that is the answer, all right.
So that's how many portions a day would come down to quarter hours.
So let's see if we can build a little formula over here back in our original dataset.
We'll use that number and copy it over here and paste, all right.
So I'm going call this “QtrHour” and the formula is going be =MOD([ @When ],1) What's that going to do?
That's going take off any whole days and give me just the time.
All right.
I'm not going use round I'm going use something called MROUND.
I think that stands for like multiple round to a certain multiple and normally I would use this to round up to the nearest 5 or 10 or something like that.
But very interesting what I'm going to do is I'm going to use that number, 1 divided by 96.
The formula is now =MROUND(MOD([ @When ],1),(1/96)) Let's see if this actually worked perfectly for us.
I'm going to format that column Ctrl+1.
We will go into Time.
Actually let's do one that shows the seconds.
Click OK and you'll see that we've perfectly rounded everything now to the nearest quarter hour.
Now by the way Michael had asked, “hey, would PowerPivot do this?” While PowerPivot adds a lot of date and time intelligence and actually look it added a lot of date intelligence not a lot of time intelligence so you'd have to do the exact same thing in PowerPivot.
So PowerPivot could solve it but using this exact same method.
So let's just stay here.
We'll get rid of our other PivotTable.
I didn't need to do that I could have just refreshed but Insert PivotTable, click OK and now we can put quarter hour in row labels and count in values so we get to see how many items occurred in each quarter hour.
All right, so Michael I know you were looking for something better, something building the PivotTables and unfortunately it's just not there.
PowerPivot doesn't add it either but this formula here, =MROUND(MOD([ @When ],1),(1/96)) will get you back into the quarter hour Okay well hey I want to thank everyone for stopping by.
I want to thank Michael for sending that question in.
We’ll see you next time for another netcast from MrExcel.
