Select "Time Description" based on current date and time in Google Sheets (VLookup, Query)

Question

I am trying to use VLookup or QUERY to extract a day and time description based on the current date and time. I thought QUERY may be the best here, but VLOOKUP seems to be the best solution perhaps.

Is it possible to extract the date from between date and time ranges? The descriptions may change frequently so they can't be hardcoded into the formula.

Here is the Google Sheet, which can also be copied. https://docs.google.com/spreadsheets/d/1efQo1t9FJbItYdf1OQJ4vzeG1bUaQ1R0vbFGsgIjzOI/edit?usp=sharing

This was the current formula, which I hoped would extract the correct date and timeslot description

=ARRAYFORMULA(IFERROR(TEXT(A2:A, "dddd ")&VLOOKUP(TIMEVALUE(A2:A), {
 TIMEVALUE(IFERROR(REGEXEXTRACT(TimeDescription!A2:A, "(.*) -"))), TimeDescription!B2:B}, 2, 1)))

Everything will get much simpler if, on the TimeDescription tab you break the "time slots" column into two columns, a time start and time end column. Is that a change you can make? Also can you make the sheet (or a copy of it) editable so we can test/demo some ideas? — MattKing
Linking this spreadsheet as is isn't useful. use multiple hard coded times. Then highlight what's wrong & what you expect alongside in the spreadsheet. I suspect, only on certain times of the day will the spreadsheet error out! You need to illuminate when that is! — JGFMK
@Gracie, indeed i could. But if i did that for every sheet I help people on, i'd clog up my Drive within a few days. If i make a copy and give you an answer based on that copy, i think there is a strong chance that it doesn't work on your real sheet. But that 's what i'll do for now. I guess i don't see the downside of making your sheet public if it's just a copy. you could protect the original tab? — MattKing
@MattKing btw all google spreadsheets are size-less. you can have thousands of sheets and they will occupy 0 MB of your drive. — player0

player0 player0 · Accepted Answer · 2020-01-13T22:38:10

based on your time data logic try:

=ARRAYFORMULA(IFERROR(TEXT(A2:A, "dddd ")&VLOOKUP(TIMEVALUE(A2:A), {
 TIMEVALUE("00:00"), "Evening";
 TIMEVALUE("07:00"), "Breakfast";
 TIMEVALUE("09:00"), "Morning";
 TIMEVALUE("16:00"), "Daytime";
 TIMEVALUE("20:00"), "Evening"}, 2, 1)))

for non-hardcoded references use:

=ARRAYFORMULA(IFERROR(TEXT(A2:A, "dddd ")&VLOOKUP(TIMEVALUE(A2:A), 
 SORT({TIMEVALUE(REGEXEXTRACT(TimeDescription!A2:A, "(.*) -")), 
 TimeDescription!B2:B}, 1, 1), 2, 1)))

Select "Time Description" based on current date and time in Google Sheets (VLookup, Query)

2 Answers

spreadsheet demo