If time is between ...., then text (region)

[tkeiffer]

Looking to leverage either a formula, macro or even a conversion and VLOOKUP.

I have a cell A1 that contains a data/time in this format which is in EST. Here are two examples:

8/9/2023 14:04
8/8/2023 6:14

8/9/2023 6:14​

8/9/2023 14:04​

The date is irrelevant to me and I'm only interested in the timestamp. In a different cell, B1 for example, I'm looking to convert this to one of three global region/zones. If the timestamp is between 8am and 7pm, then "Americas", if timestamp is between 7:01 pm and 2:00am, then "APAC", if timestamp is between 2:01am and 7:59am, then "EMEA".

Perhaps I can convert the timestamp to a number and do a vlookup in a different tab in the workbook. Maybe there's some sort of nested IF statement that can figure it out. I've tried a number of different approaches but with no success. Can someone help me??? It would be much appreciated!

 

[awoohaw]

Try this:

Mr excel questions 66.xlsm
	A	B
1	2023-08-09 06:14	EMEA
2	2023-08-09 14:04	Americas
tkeiffer

Cell Formulas
Range	Formula
B1:B2	B1	=IF(HOUR(A1)<=2,"APAC",IF(HOUR(A1)<=8,"EMEA",IF(HOUR(A1)<19,"Americas","APAC")))

 

[tkeiffer]

This is perfect. Thank you so much

 

[awoohaw]

My pleasure. I am happy you found a solution here.

Best Wishes!