# 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.xlsxJKLMNO113:52:19Daytime23:00:000:00:00Late Fringe21:38:33Late Fringe0:00:011:59:59Late Fringe318:41:08Early Fringe2:00:005:59:59Overnight411:48:56Daytime6:00:008:59:59Morning512:47:00Daytime9:00:0014:59:59Daytime66:39:47Morning15:00:0018:59:59Early Fringe78:40:18Morning19:00:0019:59:59Prime Access816:49:07Early Fringe20:00:0022:59:59Prime92:43:34Overnight1023:39:11Late Fringe111:36:05Late FringeSheet9Cell FormulasRangeFormulaK1: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

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

20 03 14.xlsmJKLMNO113:52:19Daytime00:00:0001:59:59Late Fringe201:38:33Late Fringe02:00:0005:59:59Overnight318:41:08Early Fringe06:00:0008:59:59Morning411:48:56Daytime09:00:0014:59:59Daytime512:47:00Daytime15:00:0018:59:59Early Fringe606:39:47Morning19:00:0019:59:59Prime Access708:40:18Morning20:00:0022:59:59Prime816:49:07Early Fringe23:00:0000:00:00Late Fringe902:43:34Overnight1023:39:11Late Fringe1101:36:05Late FringeSheet2 (2)Cell FormulasRangeFormulaK1:K11K1=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

MSWIT310 said:


> 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.xlsmBCDEFGHIJKLMNO11:52:19 PMDaytime12:00:00 AM1:59:59 AMLate Fringe1:52:19 PMDaytime11:00:00 PM11:59:59 PMLate Fringe21:38:33 AMLate Fringe2:00:00 AM5:59:59 AMOvernight1:38:33 AMLate Fringe12:00:00 AM1:59:59 AMLate Fringe36:41:08 PMEarly Fringe6:00:00 AM8:59:59 AMMorning6:41:08 PMEarly Fringe2:00:00 AM5:59:59 AMOvernight411:48:56 AMDaytime9:00:00 AM2:59:59 PMDaytime11:48:56 AMDaytime6:00:00 AM8:59:59 AMMorning512:47:00 PMDaytime3:00:00 PM6:59:59 PMEarly Fringe12:47:00 PMDaytime9:00:00 AM2:59:59 PMDaytime66:39:47 AMMorning7:00:00 PM7:59:59 PMPrime Access6:39:47 AMMorning3:00:00 PM6:59:59 PMEarly Fringe78:40:18 AMMorning8:00:00 PM10:59:59 PMPrime8:40:18 AMMorning7:00:00 PM7:59:59 PMPrime Access84:49:07 PMEarly Fringe11:00:00 PM12:00:00 AMLate Fringe4:49:07 PMEarly Fringe8:00:00 PM10:59:59 PMPrime92:43:34 AMOvernight2:43:34 AMOvernight1011:39:11 PMLate Fringe11:39:11 PMLate Fringe111:36:05 AMLate Fringe1:36:05 AMLate Fringe1211:36:05 PMLate Fringe11:36:05 PMLate Fringe1311:00:00 PMLate Fringe11:00:00 PMLate Fringe1412:00:00 AMLate Fringe12:00:00 AMLate Fringe1511:59:59 PMLate Fringe11:59:59 PMLate Fringe1612:00:01 AMLate Fringe12:00:01 AMLate Fringe1710:59:59 PMPrime10:59:59 PMPrimeSheet35Cell FormulasRangeFormulaC1: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

MSWIT310 said:


> 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.


----------



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


----------



## MSWIT310

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.


----------



## KRice

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.xlsmABCDEFGHIJKLMNO11:52:19 PMDaytime12:00:00 AM1:59:59 AMLate Fringe1:52:19 PMDaytime11:00:00 PM11:59:59 PMLate Fringe21:38:33 AMLate Fringe2:00:00 AM5:59:59 AMOvernight1:38:33 AMLate Fringe12:00:00 AM1:59:59 AMLate Fringe36:41:08 PMEarly Fringe6:00:00 AM8:59:59 AMMorning6:41:08 PMEarly Fringe2:00:00 AM5:59:59 AMOvernight411:48:56 AMDaytime9:00:00 AM2:59:59 PMDaytime###########Daytime6:00:00 AM8:59:59 AMMorning512:47:00 PMDaytime3:00:00 PM6:59:59 PMEarly Fringe###########Daytime9:00:00 AM2:59:59 PMDaytime66:39:47 AMMorning7:00:00 PM7:59:59 PMPrime Access6:39:47 AMMorning3:00:00 PM6:59:59 PMEarly Fringe78:40:18 AMMorning8:00:00 PM10:59:59 PMPrime8:40:18 AMMorning7:00:00 PM7:59:59 PMPrime Access84:49:07 PMEarly Fringe11:00:00 PM12:00:00 AMLate Fringe4:49:07 PMEarly Fringe8:00:00 PM10:59:59 PMPrime92:43:34 AMOvernight2:43:34 AMOvernight1011:39:11 PMLate Fringe###########Late Fringe111:36:05 AMLate Fringe1:36:05 AMLate Fringe1211:36:05 PMLate Fringe###########Late Fringe1311:00:00 PMLate Fringe###########Late Fringe1412:00:00 AMLate Fringe###########Late Fringe1511:59:59 PMLate Fringe###########Late Fringe1612:00:01 AMLate Fringe###########Late Fringe1710:59:59 PMPrime###########Prime18192011:30:00 PMPrimePeter's formula with Kirk's lookup table2111:30:00 PMLate FringePeter's formula with Peter's lookup table2211:30:00 PMLate FringeKirk's formula with Kirk's lookup table2311:30:00 PMLate FringeKirk's formula with Peter's lookup tableMSWIT310Cell FormulasRangeFormulaC21,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

You're welcome...we're glad to help.


----------



## MSWIT310

Hello, I've been using this wonderful formula for 2+ years now, and today, it stopped working. When I paste in the formula "=INDEX(N$1:N$8,SUMPRODUCT((I13>=L$1:L$8)*(I13<=M$1:M$8)*ROW(N$1:N$8)))" it will paste in all the dayparts - not actually using the formula. Do you have ANY idea as to why or how to fix this?


----------



## KRice

I don't see any issue. Could you post a minisheet using XL2BB?...or alternatively, show the formula for one of the cells that is clearly associated with an incorrect result (e.g., 3:17:36 AM should be "Overnight"...what formula appears in your cell such that "Prime Access" is returned?).
MrExcel_20221214.xlsxIJKLMN111:00:00 PM11:59:59 PMLate Fringe212:00:00 AM1:59:59 AMLate Fringe32:00:00 AM5:59:59 AMOvernight46:00:00 AM8:59:59 AMMorning59:00:00 AM2:59:59 PMDaytime63:00:00 PM6:59:59 PMEarly Fringe77:00:00 PM7:59:59 PMPrime Access88:00:00 PM10:59:59 PMPrime9101112Occurrence Start TimeDaypart1311:27:53 PMLate Fringe1411:56:13 PMLate Fringe154:55:48 AMOvernight166:38:45 PMEarly Fringe1712:58:38 AMLate Fringe186:45:05 PMEarly Fringe193:17:36 AMOvernight203:33:30 PMEarly Fringe213:36:53 PMEarly Fringe229:12:59 AMDaytime2312:22:26 PMDaytimeMSWIT310_20221213Cell FormulasRangeFormulaJ13:J23J13=INDEX(N$1:N$8,SUMPRODUCT((I13>=L$1:L$8)*(I13<=M$1:M$8)*ROW(N$1:N$8)))


----------



## Peter_SSs

MSWIT310 said:


> Do you have ANY idea as to why or how to fix this?


It looks to me like two things could be involved here (though remember that I do not have a mac to check)

The fact that those values automatically 'spilled' down the column indicated that your profile may no longer be correct (unless Excel 2019 now 'spills' results?)


That behaviour would occur (in Excel 365 for example) if some of the times are actual times (*numbers*) and some of the times are *text*. In the sample below, all the times in columns L & M are actual times (numerical) but you see different results in the bottom section depending on whether those times ate numerical or not. With the formula in H13, because the 'time' is actually *text*, the formula is not finding that 'time' between any of the column L & M values (text values are always > numerical values in Excel) so the SUMPRODUCT is returning 0. When you index a column with the value 0, it returns the whole column, which is what has happened in column H

22 12 14.xlsmGHIJKLMN111:00:00 PM11:59:59 PMLate Fringe212:00:01 AM1:59:59 AMLate Fringe32:00:00 AM5:59:59 AMOvernight46:00:00 AM8:59:59 AMMorning59:00:00 AM2:59:59 PMRaytime63:00:00 PM6:59:59 PMEarly Fringe77:00:00 PM7:59:59 PMPrime Access88:00:00 PM10:59:59 PMPrime Access9101112FALSETRUE1311:27:53 PMLate Fringe11:27:53 PMLate Fringe14Late Fringe15Overnight16Morning17Raytime18Early Fringe19Prime Access20Prime Access21TestCell FormulasRangeFormulaG12,I12G12=ISNUMBER(G13)H13:H20H13=INDEX(N$1:N$8,SUMPRODUCT((G13>=L$1:L$8)*(G13<=M$1:M$8)*ROW(N$1:N$8)))J13J13=INDEX(N$1:N$8,SUMPRODUCT((I13>=L$1:L$8)*(I13<=M$1:M$8)*ROW(N$1:N$8)))Dynamic array formulas.


----------

