SOLVED

Create time slot

Occasional Contributor

Create time slot

Hello, I have a question here, thank you very much in advance.
I would like to identify a column of dates within a band, so for example if my column K is between 07:00:00-13:59:59:59 it would be called "MATINAL", if it is between 14:00:00-16:59:59:59 it would be called "SOBREMESA", etc.

But don't know how to do it

I would appreciate any light on this matter

 

Thanks anyway

 

 

afrutos_0-1650283875784.pngafrutos_1-1650283894170.png

 

3 Replies

@afrutos 

I'd use Data > Text to Columns to split the time ranges in column K into a start time and end time.

Same for the Horario Franja values.

Will each range in column K always fall entirely within one time slot? If so, you can use INDEX/MATCH or XLOOKUP.

See the attached sample.

 

@Hans Vogelaar 

 

Yes, it will fall inside the band, I'm trying to apply it in the same way you indicated but it doesn't work the same, I don't know if it's because of the format of the cells.

 

'm not receiving the correct result

 

afrutos_0-1650288706476.png

 

best response confirmed by afrutos (Occasional Contributor)
Solution

@afrutos 

The values in columns J and K are left-aligned. suggesting that they are text values, not real time values.

Try to convert them to real time values.

And the lookup table in columns N to P should be as in my sample workbook, with NOCHE at the top.