Search code examples
datedatetimegoogle-sheetsgoogle-sheets-formulaifs

Google sheets: Returns "False" if the time is in the range from 23:00 to 05:00 (or from 11:00 PM to 05:00 AM)

My formula should return 4 different text values depending on what time value is in the cell.

Everything would work fine, but every time I try to make the formula look at the time from 23:00 to 05:00, it always returns False or #N/A if in full formula. I tried in 12 hours format but it works the same way.

=IFS(
AND(TEXT(C18,"HH:MM")>="05:00",TEXT(C18,"HH:MM")<="10:59"),"SS",
AND(TEXT(C18,"HH:MM")>="11:00",TEXT(C18,"HH:MM")<="16:59"),"AA",
AND(TEXT(C18,"HH:MM")>="17:00",TEXT(C18,"HH:MM")<="22:59"),"MM",
AND(TEXT(C18,"HH:MM")>="23:00",TEXT(C18,"HH:MM")<="04:59"),"LL")

Everything works correctly in this formula except for the last line. Instead of LL, # N/A returns. And I don't understand why this is so.

Solution

  • try:

    =VLOOKUP(C18, {
 0,              "LL"; 
 VALUE("5:00"),  "SS";
 VALUE("11:00"), "AA";
 VALUE("17:00"), "MM";
 VALUE("23:00"), "LL"}, 2, 1)

    enter image description here

    or:

    =VLOOKUP(TIMEVALUE(C18), {
 TIMEVALUE("00:00"), "LL"; 
 TIMEVALUE("05:00"), "SS";
 TIMEVALUE("11:00"), "AA";
 TIMEVALUE("17:00"), "MM";
 TIMEVALUE("23:00"), "LL"}, 2, 1)

    enter image description here