exceltoxl.com

Hi 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,