Excel and Time Ranges

[jglanz]

HI, I'm trying to figure out the formula for this. If let's say 9:01 Am falls in between 9:00am and 4:00pm then assign it to EM. And then continuing if a time falls between 4-8pm and then 8pm to 12am and so forth. Can't seem to crack the formula here, I assumed doing a vlookup would do the trick but maybe the times are not in the correct format. Not sure, any help would be greatly appreciated.

 

[Joe4]

Please show us a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ExceLoki]

here's a couple of possible solutions
------------

Book1
	A	B	C	D	E
2	9:00:00 AM		9:01:00 AM	EM	if
3	4:00:00 PM		9:01:00 AM	EM	ifs
4	8:00:00 PM
5	12:00:00 AM
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IF(AND(C2>=$A$2,C2<$A$3),"EM",IF(AND(C2>=$A$3,C2<$A$4),"EM2",IF(AND(C2>=$A$4,C2<$A$5),"EM3","EM4")))
D3	D3	=IFERROR(IFS(AND(C3>=$A$2,C3<$A$3),"EM",AND(C3>=$A$3,C3<$A$4),"EM2",AND(C3>=$A$4,C3<$A$5),"EM3"),"EM4")