How to create Bucket with help of If Functions

[Gaurang Mhatre]

Dear Friends,

i have this list where i have to put as per the timings given.
Bucket for below timing is 9,2 and 6:30 respectively
17:00:01 23:00:00 is 9 am

23:00:01 12:00:00 is 2pm

12:00:01 17:00:00 is 6:30pm

Just wanted to know how to apply if and formula for the above criteria.

Thanks in advance for your help.

Regards,
Gaurang

 

[PaulF]

=IF(AND(D2>=TIMEVALUE("17:00:01"),D2<=TIMEVALUE("23:00:0")),TIMEVALUE("09:00:00"),IF(AND(D2>=TIMEVALUE("23:00:01"),D2<=TIMEVALUE("12:00:00")),TIMEVALUE("14:00:00"),TIMEVALUE("18:30:00")))

 

[Gaurang Mhatre]

Hi Paul,

Thanks for taking out time,
But there is error reflecting for few timings.
Attached is excel for same.

Regards,
Gaurang

 

[PaulF]

=IF(AND(A2>=TIMEVALUE("17:00:01"),A2<=TIMEVALUE("23:00:0")),TIMEVALUE("09:00:00"),IF(OR(A2>=TIMEVALUE("23:00:01"),A2<=TIMEVALUE("12:00:00")),TIMEVALUE("14:00:00"),TIMEVALUE("18:30:00")))

The OR was AND... Sorry about that...

Respectfully,
PaulF

 

[Khalid NGO]

Hi,
Not sure this is okay:
=VALUE(LOOKUP(VALUE(D2),{0.00013,0.70833,0.95834,0.5},{"2:00:00 PM","9:00:00 AM","6:30:00 PM","2:00:00 PM"}))

Can you confirm plz?

Regards,