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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,057
Office Version
  1. 2019
Platform
  1. Windows
You might try something like this:

Book2.xlsx
JKLMNO
113:52:19Daytime23:00:000:00:00Late Fringe
21:38:33Late Fringe0:00:011:59:59Late Fringe
318:41:08Early Fringe2:00:005:59:59Overnight
411:48:56Daytime6:00:008:59:59Morning
512:47:00Daytime9:00:0014:59:59Daytime
66:39:47Morning15:00:0018:59:59Early Fringe
78:40:18Morning19:00:0019:59:59Prime Access
816:49:07Early Fringe20:00:0022:59:59Prime
92:43:34Overnight
1023:39:11Late Fringe
111:36:05Late Fringe
Sheet9
Cell Formulas
RangeFormula
K1:K11K1=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,349
Office Version
  1. 365
Platform
  1. Windows
I have also split the Late Fringe range, but in a slightly different format.

20 03 14.xlsm
JKLMNO
113:52:19Daytime00:00:0001:59:59Late Fringe
201:38:33Late Fringe02:00:0005:59:59Overnight
318:41:08Early Fringe06:00:0008:59:59Morning
411:48:56Daytime09:00:0014:59:59Daytime
512:47:00Daytime15:00:0018:59:59Early Fringe
606:39:47Morning19:00:0019:59:59Prime Access
708:40:18Morning20:00:0022:59:59Prime
816:49:07Early Fringe23:00:0000:00:00Late Fringe
902:43:34Overnight
1023:39:11Late Fringe
1101:36:05Late Fringe
Sheet2 (2)
Cell Formulas
RangeFormula
K1:K11K1=VLOOKUP(J1,M$1:O$8,3)
 

MSWIT310

New Member
Joined
Mar 13, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
THANK YOU A MILLION TIMES OVER GENTLEMEN.

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,349
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Attachments

  • Screen Shot 2020-04-15 at 4.48.58 PM.png
    Screen Shot 2020-04-15 at 4.48.58 PM.png
    22.1 KB · Views: 5

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,057
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
BCDEFGHIJKLMNO
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 PMDaytime11:48:56 AMDaytime6:00:00 AM8:59:59 AMMorning
512:47:00 PMDaytime3:00:00 PM6:59:59 PMEarly Fringe12:47:00 PMDaytime9: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 Fringe11:39:11 PMLate Fringe
111:36:05 AMLate Fringe1:36:05 AMLate Fringe
1211:36:05 PMLate Fringe11:36:05 PMLate Fringe
1311:00:00 PMLate Fringe11:00:00 PMLate Fringe
1412:00:00 AMLate Fringe12:00:00 AMLate Fringe
1511:59:59 PMLate Fringe11:59:59 PMLate Fringe
1612:00:01 AMLate Fringe12:00:01 AMLate Fringe
1710:59:59 PMPrime10:59:59 PMPrime
Sheet35
Cell Formulas
RangeFormula
C1:C17C1=VLOOKUP(B1,E$1:G$8,3)
K1:K17K1=INDEX(O$1:O$8,SUMPRODUCT((J1>=M$1:M$8)*(J1<=N$1:N$8)*ROW(O$1:O$8)))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,349
Office Version
  1. 365
Platform
  1. Windows
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

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

Well-known Member
Joined
Dec 9, 2003
Messages
1,057
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,750
Members
415,854
Latest member
Tutu123

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