选择“时间描述".根据Google表格中的当前日期和时间(VLookup，查询) [英] Select "Time Description" based on current date and time in Google Sheets (VLookup, Query)

问题描述

我正在尝试使用VLookup或QUERY来基于当前日期和时间提取日期和时间描述.我以为QUERY可能是最好的，但是VLOOKUP似乎是最好的解决方案.

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.

这是Google表格，也可以复制.

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

推荐答案

根据您的时间数据逻辑尝试:

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

这篇关于选择“时间描述".根据Google表格中的当前日期和时间(VLookup，查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！