IF time is between two times, return multiple values

[MSWIT310]

Within a set of time constraints, I am trying to create a formula that will allow me to identify IF a time is between two times, to assign it a text value. I would like to do this for multiple values. The data in M/N/O is the value set for output. Column J is my example of times to use. Column K is my formula entry which has never produced an accurate result. This seems like it should be possible!! What am I missing!! Nothing is working!

Here are a few of the 20+ examples I've tried all day. Initially, I was entering the data in M/N/O by hand, but realized that entering by cell could be easier:

=IF(J4>=$M$2,J4<=$N$2,$O$2),IF(J4>=$M$1,J4<=$N$1,$O$1),IF(J4>=$M$3,J4<=$N$3,$O$3),IF(J4>=$M$4,J4<=$N$4,$O$4),IF(J4>=$M$5,J4<=$N$5,$O$5),IF(J4>=$M$6,J4<=$N$6,$O$6),IF(J4>=$M$7,J4<=$N$7,$O$7)

=IF(J4>$M$2,J4<$N$2,$O$2),IF(J4>$M$1,J4<$N$1,$O$1),IF(J4>$M$3,J4<$N$3,$O$3),IF(J4>$M$4,J4<$N$4,$O$4),IF(J4>$M$5,J4<$N$5,$O$5),IF(J4>$M$6,J4<$N$6,$O$6),IF(J4>$M$7,J4<$N$7,$O$7)

=IF(J4>=$M$2,J4<=$N$2,$O$2),IF(J4>=$M$1,J4<=$N$1,$O$1),IF(J4>=$M$3,J4<=$N$3,$O$3),IF(J4>=$M$4,J4<=$N$4,$O$4),IF(J4>=$M$5,J4<=$N$5,$O$5),IF(J4>=$M$6,J4<=$N$6,$O$6),IF(J4>=$M$7,J4<=$N$7,$O$7)))))))

=IF(AND(J4>=$M$2,J4<$N$2,$O$2),IF(AND(J4>=$M$1,J4<$N$1,$O$1),IF(AND(J4>=$M$3,J4<$N$3,$O$3),IF(AND(J4>=$M$4,J4<$N$4,$O$4),IF(AND(J4>=$M$5,J4<$N$5,$O$5),IF(AND(J4>=$M$6,J4<$N$6,$O$6),IF(AND(J4>=$M$7,J4<$N$7,$O$7)))))))

=IF(AND(J4>=$M$2,J4<$N$2,$O$2),IF(AND(J4>=$M$1,J4<$N$1,$O$1),IF(AND(J4>=$M$3,J4<$N$3,$O$3),IF(AND(J4>=$M$4,J4<$N$4,$O$4),IF(AND(J4>=$M$5,J4<$N$5,$O$5),IF(AND(J4>=$M$6,J4<$N$6,$O$6),IF(AND(J4>=$M$7,J4<$N$7,$O$7))))))))

-- Prior to entering the times in columns, I tried time value as well:
=IF((J5=TIME(20,0,0),J5<=TIME(22,59,59)),”Prime”,IF((J5=TIME(19,0,0),J5<=TIME(19,59,59)),”Prime Access”,IF((J5=TIME(15,0,0),J5<=TIME(18,59,59)),”Early Fringe”, IF((J5=TIME(9,0,0),J5<=TIME(14,59,59)),”Day”, IF((J5=TIME(6,0,0),J5<=TIME(8,59,59)),”Morning”, IF((J5=TIME(2,0,0),J5<=TIME(5,59,59)),”Overnight”, IF((J5=TIME(23,0,0),J5<=TIME(1,59,59)),”Late Fringe”)))))))


I appreciate any assistance. I have 10k+ rows that I will need to enter this for, so the formula creation will be worth days of time.
THANK YOU!

 

[KRice]

You might try something like this:

Book2.xlsx
	J	K	L	M	N	O
1	13:52:19	Daytime		23:00:00	0:00:00	Late Fringe
2	1:38:33	Late Fringe		0:00:01	1:59:59	Late Fringe
3	18:41:08	Early Fringe		2:00:00	5:59:59	Overnight
4	11:48:56	Daytime		6:00:00	8:59:59	Morning
5	12:47:00	Daytime		9:00:00	14:59:59	Daytime
6	6:39:47	Morning		15:00:00	18:59:59	Early Fringe
7	8:40:18	Morning		19:00:00	19:59:59	Prime Access
8	16:49:07	Early Fringe		20:00:00	22:59:59	Prime
9	2:43:34	Overnight
10	23:39:11	Late Fringe
11	1:36:05	Late Fringe
Sheet9

Cell Formulas
Range	Formula
K1:K11	K1	=INDEX(O$1:O$8,SUMPRODUCT((J1>=M$1:M$8)*(J1<=N$1:N$8)*ROW(O$1:O$8)))

Note that I split your Late Fringe definition into two separate ranges. The greater/less than comparisons do not work well when crossing over the 00:00:00 threshold, so that time block has two components. You might also examine the rules at the limits of each time range. I adopted <= and >= as suggested by your formulas.

 

[Peter_SSs]

I have also split the Late Fringe range, but in a slightly different format.

20 03 14.xlsm
	J	K	L	M	N	O
1	13:52:19	Daytime		00:00:00	01:59:59	Late Fringe
2	01:38:33	Late Fringe		02:00:00	05:59:59	Overnight
3	18:41:08	Early Fringe		06:00:00	08:59:59	Morning
4	11:48:56	Daytime		09:00:00	14:59:59	Daytime
5	12:47:00	Daytime		15:00:00	18:59:59	Early Fringe
6	06:39:47	Morning		19:00:00	19:59:59	Prime Access
7	08:40:18	Morning		20:00:00	22:59:59	Prime
8	16:49:07	Early Fringe		23:00:00	00:00:00	Late Fringe
9	02:43:34	Overnight
10	23:39:11	Late Fringe
11	01:36:05	Late Fringe
Sheet2 (2)

Cell Formulas
Range	Formula
K1:K11	K1	=VLOOKUP(J1,M$1:O$8,3)

 

[MSWIT310]

THANK YOU A MILLION TIMES OVER GENTLEMEN.

You've saved me hours. Thank you thank you!

 

[Peter_SSs]

THANK YOU A MILLION TIMES OVER GENTLEMEN.

You've saved me hours. Thank you thank you!

You're very welcome. Glad you were able to get something suitable.

BTW, "Welcome to the MrExcel board!"

 

[MSWIT310]

Hello again!

I've realized i've come across a little snafu.

I'm going to assume that you are on a PC, and I am on a Mac. (maybe). The reason I say this is that I just gave myself the quick lessons on time and 1900 vs 1904!

As I was figuring out this issue, I noticed that for whatever reason, the hours of 11p-12a are still calculating as prime. And, I havent' a clue why. I went back to manually adjusting (so please know that your formulas are still saving me HOURS). Figured worth a quick ask.

Screenshot shows the formulas you helped me with me.
Thanks!

 

[KRice]

That's interesting. Could you post the formula from your worksheet? I can't reproduce that result. After having another look at my previous offering, I made some adjustments to the time reference block, so that every interval begins on an hour (00 seconds) and ends at some other time with 59 seconds. This addresses one error I saw for a 12:00:00 AM time, which returned an error using my previous solution. In the sheet below, you'll see my revised version on the right, and Peter's previous one is on the left. Both seem to work find for the 11 P - 12 A time period, so I'm curious about whether your formula might be pointing to an incorrect look up range.

mrexcel_20200408.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1:52:19 PM	Daytime		12:00:00 AM	1:59:59 AM	Late Fringe			1:52:19 PM	Daytime		11:00:00 PM	11:59:59 PM	Late Fringe
2	1:38:33 AM	Late Fringe		2:00:00 AM	5:59:59 AM	Overnight			1:38:33 AM	Late Fringe		12:00:00 AM	1:59:59 AM	Late Fringe
3	6:41:08 PM	Early Fringe		6:00:00 AM	8:59:59 AM	Morning			6:41:08 PM	Early Fringe		2:00:00 AM	5:59:59 AM	Overnight
4	11:48:56 AM	Daytime		9:00:00 AM	2:59:59 PM	Daytime			11:48:56 AM	Daytime		6:00:00 AM	8:59:59 AM	Morning
5	12:47:00 PM	Daytime		3:00:00 PM	6:59:59 PM	Early Fringe			12:47:00 PM	Daytime		9:00:00 AM	2:59:59 PM	Daytime
6	6:39:47 AM	Morning		7:00:00 PM	7:59:59 PM	Prime Access			6:39:47 AM	Morning		3:00:00 PM	6:59:59 PM	Early Fringe
7	8:40:18 AM	Morning		8:00:00 PM	10:59:59 PM	Prime			8:40:18 AM	Morning		7:00:00 PM	7:59:59 PM	Prime Access
8	4:49:07 PM	Early Fringe		11:00:00 PM	12:00:00 AM	Late Fringe			4:49:07 PM	Early Fringe		8:00:00 PM	10:59:59 PM	Prime
9	2:43:34 AM	Overnight							2:43:34 AM	Overnight
10	11:39:11 PM	Late Fringe							11:39:11 PM	Late Fringe
11	1:36:05 AM	Late Fringe							1:36:05 AM	Late Fringe
12	11:36:05 PM	Late Fringe							11:36:05 PM	Late Fringe
13	11:00:00 PM	Late Fringe							11:00:00 PM	Late Fringe
14	12:00:00 AM	Late Fringe							12:00:00 AM	Late Fringe
15	11:59:59 PM	Late Fringe							11:59:59 PM	Late Fringe
16	12:00:01 AM	Late Fringe							12:00:01 AM	Late Fringe
17	10:59:59 PM	Prime							10:59:59 PM	Prime
Sheet35

Cell Formulas
Range	Formula
C1:C17	C1	=VLOOKUP(B1,E$1:G$8,3)
K1:K17	K1	=INDEX(O$1:O$8,SUMPRODUCT((J1>=M$1:M$8)*(J1<=N$1:N$8)*ROW(O$1:O$8)))

 

[Peter_SSs]

I'm going to assume that you are on a PC, and I am on a Mac. (maybe)

I don't have a Mac so any issues surrounding that I will not be able to assist with.

 

[MSWIT310]

Crazy. This really oddly will come down to a PC vs. Mac, and 1900 dates vs 1904 (which, at least now I know to check that first, but still quite confused on the why....)

Anywho, the original formula I posted was: =VLOOKUP(J1,M$1:O$8,3)

I just tried your Index formula

=INDEX(O$1:O$8,SUMPRODUCT((J1>=M$1:M$8)*(J1<=N$1:N$8)*ROW(O$1:O$8)))

and, this looks to correct the late fringe issue! I have no idea why, but I'll take it!!

Thank you both Kirk & peter. Such a weird glitch but I'm glad I have an alternative!

 

[KRice]

I'm curious. If you were using VLOOKUP without the last optional argument, the function defaults to treating that missing argument as TRUE, which means the first column of the lookup table of times should be sorted in ascending order. That's why Peter's solution split the Late Fringe period differently compared to my ordering. My solution doesn't require any particular order. If you used my lookup table with Peter's formula, some unexpected results might occur.