Hourly TAT Age Bucket

[sanket_sk]

Dear All,

We tracking our order shipment progress on hourly TAT ( Turn Around Time).

Example

TAT Bucket
20:54 <24Hrs
23:59 <24Hrs
38:03 >24Hrs <48Hrs

These times can be starting with 00:01hrs till 8736:30hrs ( 364 days) , currently i am applying these buckets manually ( by filtering Hrs), can anybody help me in creating formula for these bucket?

Buckets will be as follows ...

<=24Hrs
>24 to 48Hrs
>48 to <72Hrs
>72 to 120Hrs
>120 - <168
>7days <15 Days
>15 Days

Thanks & Regards,
Sanket

 

[FormR]

Hi, with a date/time value in A1 you could try:

Excel 2013/2016
	A	B
1	23:59:00	<=24
2	24:00:00	24 to 48
3	47:59:59	24 to 48
4	48:00:00	48 to 72
5	71:59:59	48 to 72
6	72:00:00	72 to 120
7	119:59:00	72 to 120
8	120:00:00	120 to 168
9	167:59:59	120 to 168
10	168:00:00	7 days to 15 days
11	359:59:59	7 days to 15 days
12	360:00:00	15 Days +
13	999:00:00	15 Days +
Sheet1

Cell Formulas
Range	Formula
B1	=LOOKUP(A1,{0,1,2,3,5,7,15},{"<=24","24 to 48","48 to 72","72 to 120","120 to 168","7 days to 15 days","15 Days +"})

 

[sanket_sk]

Thanks its working, may i know how this formula works

Thanks & Regards,
Sanket

 

[FormR]

may i know how this formula works

Hi, if you do a little research on how Excel stores dates and times then that along with the built-in help for the LOOKUP() function should reveal everything you need to know.