« Compare Two Lists in Excel (3 Easy and Simple ways) | Welcome to Microsoft Family » |
Collect Time in Hourly Buckets
Formulas, How-to's, Pivot TutorialHi There,
Have you ever come across how to group the time in terms of hourly basis? You understood?, Say, if you are working for an application support and few of the issues are escalated by your clients due to employee delay. To find the number of escallations raised, in total 10, in hourly basis which falls between first 0-2 hours, 2-4 hours, 4-8 hours, 8-24 hours or >24 hours. Here is a challenge we need to crack as a Reporting Analysts.
To fill the number of escallations in these buckets is a very easy task once you complete this post. If you feel can achieve this, then don't wait, REGISTER and post your comment using below comment section. Registration is absolutely free for commenters. If you are not aware don't worry, just follow me till the end of the post.
Firstly, convert the time into hourly basis use this formula
...
=(Escalated Time - Issue Posted Time)*24
This formula converts the date and time column to hourly column.
Once done, it is easy to pull the time to respective buckets. I use this formula to do so,
=IF(C2<2, "0-2 Hour Bucket",IF(AND(C2>2,C2<4),"2-4 Hour Bucket",IF(AND(C2>4,C2<8),"4-8 Hour Bucket",IF(AND(C2>8,C2<24),"8-24 Hour Bucket",">24 Hour Bucket"))))
That's it...
Now use this column to create a pivot and keep this column in ROW LABELS and ∑ VALUES.
Now check the magic. Your requirement is fullfilled, Right?
That's all for now. See you in the next post.
Thanks,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments