# Formula where time slot is condition



## foodzln (Dec 21, 2022)

I am a beginner to excel and want a formula that can help me build a column with time slots.
Eg IF A2 is between 09:00 to 12:00 then B2 = peak. IF A2 is between 12:00 to 03:00 then B2 = Mid. If A2 is between 03:00 to 06:00 then B2 = low and so on till 24:00 
Please help


----------



## etaf (Dec 21, 2022)

> = low and so on till 24:00


what do you want in those ranges - and so on does not help

=If ( AND(HOUR(A2)>=9,HOUR(A2)<=12), "peak",  IF(AND(HOUR(A2)>12,HOUR(A2)<=15), "Mid" , IF(AND(HOUR(A2)>15,HOUR(A2)<=18), "Mid" , IF ( and so on

But you have the hour in both
so 12:00 is both Peak & Mid

I have not included the hour in the 2nd term

So if we are going every 3 hours from midnight , a much easier way would be , nested IF using Hor

=IF( Hour(A2)<3 , "term 1 ", IF( Hour(A2)<6 , "term 2",  IF( Hour(A2)<9 , "term 3", IF( Hour(A2)<12 , "term 4", IF( Hour(A2)<15 , "term 5", IF( Hour(A2)<18 , "term 6", IF( Hour(A2)<21 , "term 7",  "term 8" )))))))

for example like this

just change the text - term x to whatever you want

Formula to determine shift if using a number 1,2,3 etc .xlsxAB120:30term 1 32:36term 1 44:42term 256:48term 368:54term 3711:00term 4813:06term 5915:12term 61017:18term 61119:24term 71221:30term 81323:36term 8141:42term 1 153:48term 2165:54term 2178:00term 3Sheet2Cell FormulasRangeFormulaB2:B17B2=IF(HOUR(A2)<3,"term 1 ",IF(HOUR(A2)<6,"term 2",IF(HOUR(A2)<9,"term 3",IF(HOUR(A2)<12,"term 4",IF(HOUR(A2)<15,"term 5",IF(HOUR(A2)<18,"term 6",IF(HOUR(A2)<21,"term 7","term 8"))))))
)A3:A17A3=A2+0.0875


----------



## foodzln (Dec 21, 2022)

I apologise, I wasn't clear in my question. 
09:01 to 12:00 is mid1, 12:01 to 15:00 is mid2, 15:01to 18:00 is mid3, 18:01 to 21:00 is mid4, 21:01 to 00:00 is mid5, 00:01 to 09:00 is mid6


----------



## foodzln (Dec 21, 2022)

I apologise, I wasn't clear in my question. 
09:01 to 12:00 is mid1, 12:01 to 15:00 is mid2, 15:01to 18:00 is mid3, 18:01 to 21:00 is mid4, 21:01 to 00:00 is mid5, 00:01 to 09:00 is mid6


----------



## etaf (Dec 22, 2022)

> I apologise, I wasn't clear in my question.
> 09:01 to 12:00 is mid1, 12:01 to 15:00 is mid2, 15:01to 18:00 is mid3, 18:01 to 21:00 is mid4, 21:01 to 00:00 is mid5, 00:01 to 09:00 is mid6


So not peak 

try
=IF(OR(A2=TIMEVALUE("00:00"),A2>TIMEVALUE("21:00")),"mid5",IF(A2>TIMEVALUE("18:00"),"mid4",IF(A2>TIMEVALUE("15:00"),"mid3",IF(A2>TIMEVALUE("12:00"),"mid2",IF(A2>TIMEVALUE("09:00"),"mid1","mid6")))))

Formula to determine shift if using a number 1,2,3 etc .xlsxABCDE120:00:00mid509:01 to 12:00 is mid1, 12:01 to 15:00 is mid2, 15:01to 18:00 is mid3, 18:01 to 21:00 is mid4, 21:01 to 00:00 is mid5, 00:01 to 09:00 is mid630:00:01mid642:06:01mid654:12:01mid666:18:01mid679:00:00mid689:00:01mid1911:06:01mid11012:00:00mid11112:00:01mid21214:06:01mid21315:00:00mid21415:00:01mid31517:06:01mid31617:46:00mid31718:00:00mid31818:00:01mid41919:23:00mid42020:34:00mid42121:00:00mid42221:00:01mid52322:34:00mid52423:55:00mid5250:00:00mid5Sheet2Cell FormulasRangeFormulaA4:A6,A15,A12,A9A4=A3+0.0875C2:C25C2=IF(OR(A2=TIMEVALUE("00:00"),A2>TIMEVALUE("21:00")),"mid5",IF(A2>TIMEVALUE("18:00"),"mid4",IF(A2>TIMEVALUE("15:00"),"mid3",IF(A2>TIMEVALUE("12:00"),"mid2",IF(A2>TIMEVALUE("09:00"),"mid1","mid6")))))


----------



## foodzln (Dec 22, 2022)

etaf said:


> So not peak
> 
> try
> =IF(OR(A2=TIMEVALUE("00:00"),A2>TIMEVALUE("21:00")),"mid5",IF(A2>TIMEVALUE("18:00"),"mid4",IF(A2>TIMEVALUE("15:00"),"mid3",IF(A2>TIMEVALUE("12:00"),"mid2",IF(A2>TIMEVALUE("09:00"),"mid1","mid6")))))
> ...


Thank you


----------



## etaf (Dec 22, 2022)

you are welcome


----------



## ldonkers (Dec 22, 2022)

This may work for you.

I set up a table with time slots. I then used a Xlookup() and find the time slot in the table. 





 


The in F10 is copied down

=XLOOKUP(E10,$M$13:$M$17,$N$13:$N$17,,1)

As long as you use the absolute reference for the lookup array and return array and change the match mode to 1 (selects the next largest number) it should work.c



*Absolute references*    An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells: =$A$1.


----------

