IF time is between two times, return multiple values

MSWIT310

New Member
Joined
Mar 13, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
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!

Screen Shot 2020-03-13 at 8.42.54 PM.png


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
Joined
Mar 13, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
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. :)
 

Some videos you may like

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
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
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)))
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...we're glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top