# IF time is between two times, return multiple values

#### MSWIT310

##### New Member
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!

#### MSWIT310

##### New Member
OOOOOOOOOH!!! So looking back at my screenshot, I did not in fact know that Peter's formula had to be followed exact - as in, exact order. I DID change the daypart into the two groupings, but paid no attention to the order. That's it! You hit the nail on the head!
So then in fact NOT a PC vs. Mac. Wow. Thank you for helping me to understand! I love this site.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### KRice

##### Well-known Member
See the bottom examples, rows 20:23. The correct answer is "Late Fringe", but if an unsorted table is used with VLOOKUP and the "TRUE" option, this type of error can happen.
mrexcel_20200408.xlsm
ABCDEFGHIJKLMNO
11:52:19 PMDaytime12:00:00 AM1:59:59 AMLate Fringe1:52:19 PMDaytime11:00:00 PM11:59:59 PMLate Fringe
21:38:33 AMLate Fringe2:00:00 AM5:59:59 AMOvernight1:38:33 AMLate Fringe12:00:00 AM1:59:59 AMLate Fringe
36:41:08 PMEarly Fringe6:00:00 AM8:59:59 AMMorning6:41:08 PMEarly Fringe2:00:00 AM5:59:59 AMOvernight
411:48:56 AMDaytime9:00:00 AM2:59:59 PMDaytime###########Daytime6:00:00 AM8:59:59 AMMorning
512:47:00 PMDaytime3:00:00 PM6:59:59 PMEarly Fringe###########Daytime9:00:00 AM2:59:59 PMDaytime
66:39:47 AMMorning7:00:00 PM7:59:59 PMPrime Access6:39:47 AMMorning3:00:00 PM6:59:59 PMEarly Fringe
78:40:18 AMMorning8:00:00 PM10:59:59 PMPrime8:40:18 AMMorning7:00:00 PM7:59:59 PMPrime Access
84:49:07 PMEarly Fringe11:00:00 PM12:00:00 AMLate Fringe4:49:07 PMEarly Fringe8:00:00 PM10:59:59 PMPrime
92:43:34 AMOvernight2:43:34 AMOvernight
1011:39:11 PMLate Fringe###########Late Fringe
111:36:05 AMLate Fringe1:36:05 AMLate Fringe
1211:36:05 PMLate Fringe###########Late Fringe
1311:00:00 PMLate Fringe###########Late Fringe
1412:00:00 AMLate Fringe###########Late Fringe
1511:59:59 PMLate Fringe###########Late Fringe
1612:00:01 AMLate Fringe###########Late Fringe
1710:59:59 PMPrime###########Prime
18
19
2011:30:00 PMPrimePeter's formula with Kirk's lookup table
2111:30:00 PMLate FringePeter's formula with Peter's lookup table
2211:30:00 PMLate FringeKirk's formula with Kirk's lookup table
2311:30:00 PMLate FringeKirk's formula with Peter's lookup table
MSWIT310
Cell Formulas
RangeFormula
C21,C1:C17C1=VLOOKUP(B1,E\$1:G\$8,3)
C23,K1:K17K1=INDEX(O\$1:O\$8,SUMPRODUCT((J1>=M\$1:M\$8)*(J1<=N\$1:N\$8)*ROW(O\$1:O\$8)))
C20C20=VLOOKUP(B20,M\$1:O\$8,3)
C22C22=INDEX(O\$1:O\$8,SUMPRODUCT((B22>=M\$1:M\$8)*(B22<=N\$1:N\$8)*ROW(O\$1:O\$8)))

Replies
6
Views
93
Replies
2
Views
51
Replies
7
Views
128
Replies
6
Views
83
Replies
1
Views
79

1,127,864
Messages
5,627,338
Members
416,242
Latest member
Kas O

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back