Solved: Working with Time to Categorize as a "Shift"

EightyOsBen · 03-22-2020 08:26 PM

Hello, new here but I’m relatively familiar with Excel and Google Sheets. I’m working on building an app for a company that manages group counseling sessions. I’ve been able to find a lot of solutions on the forums, YouTube, etc. but I’m having a lot of difficulty with the time data type and it seems like a lot of other users are as well.

In essence, we have a Start Time and an End Time for groups. Based on the Start Time, I need to designate a “shift” that the group falls in (i.e. Morning, Midday, Evening). Now, for the frustrating part. I’ve been able to successfully build this in Excel and Google Sheets but it’s not translating it to AppSheet. Please see below:

Formula that works in Excel and Google Sheets:
-Where cell B2 is the time, i.e. 09:00 AM. Based on this, the sheet would return “Morning (8 AM-Noon)”

=IFS(
TIME(HOUR(B2),0,0)<(12/24),“Morning (8 AM-Noon)”,
AND(
TIME(HOUR(B2),0,0)>(12/24),TIME(HOUR(B2),0,0)<(16/24)),
“Midday (Noon-4PM)”,
AND(
TIME(HOUR(B2),0,0)>(15/24),TIME(HOUR(B2),0,0)<(23/24)),
“Evening (4PM-8PM)”)

I thought it would be easy to translate to AppSheet by changing cell B2 to [Start Time], which is my column on AppSheet but it’s not working. Errors is “Parameter 1 of function HOUR is of the wrong type.” I think this is because the HOUR() function in AppSheet is designated as a duration and not an actual time value.

I also suspect that this problem is coming up is because AppSheet automatically takes my [Start Time] column and assigns a date (I’m seeing it as 12/30/1899), so when I try to extract the hour out of the time it’s also automatically giving me a date.

From my reading it also seems that the “Time” data type includes the date- this doesn’t make any sense to me as we have a “Date” data type already, so “Time” should stay as time alone.

I’m really hoping I’m just missing something here but any help would be greatly appreciated. I’ve got a few hours at least into trying to set up things related to this one field and the common denominator is dealing with anything related to time.

Thank you,
-Ben

Steve

This suggests the column type is DateTime. If you change the column type to Time, you won’t have to worry about the unwanted (and potentially problematic) date component.

Here’s your express reformatted the way I prefer (just aesthetics, not functional):

IFS(
  TIME([Start Time]) < TIME(12:00:00),
    “Morning (8AM-Noon)”,
  AND(
    TIME([Start Time]) > TIME(11:59:59),
    TIME([Start Time]) < TIME(16:00:00)
  ),
    “Midday (Noon-4PM)”,
  AND(
    TIME([Start Time]) > TIME(15:59:59),
    TIME([Start Time]) < TIME(23:59:59)
  ),
    “Evening (4PM-8PM)”,
)

If you change the column type of the Start Time column to Time, you won’t need to wrap it in TIME():

IFS(
  [Start Time] < TIME(12:00:00),
    “Morning (8AM-Noon)”,
  AND(
    [Start Time] > TIME(11:59:59),
    [Start Time] < TIME(16:00:00)
  ),
    “Midday (Noon-4PM)”,
  AND(
    [Start Time] > TIME(15:59:59),
    [Start Time] < TIME(23:59:59)
  ),
    “Evening (4PM-8PM)”,
)

Wrapping literal times in TIME() is unnecessary, but it is good idea to put literal times in quotes:

IFS(
  [Start Time] < "12:00:00",
    “Morning (8AM-Noon)”,
  AND(
    [Start Time] > "11:59:59",
    [Start Time] < "16:00:00"
  ),
    “Midday (Noon-4PM)”,
  AND(
    [Start Time] > "15:59:59",
    [Start Time] < "23:59:59"
  ),
    “Evening (4PM-8PM)”,
)

The logic in your expression could be simplified as well.

Each condition in an IFS() expression is mutually exclusive and evaluated in order. If the first condition is TRUE, no other conditions are evaluated. Only if the first condition is FALSE is the second condition evaluated.

Your first condition asks, is the start time before 12:00? Your second condition asks, is the start time after 11:59? If the second condition is evaluated, we know the first condition was FALSE, meaning we know the start time is not before 12:00, which is the same as saying it is after 11:59. So asking whether it’s after 11:59 is unneeded–we know it is because the first condition was FALSE.

Given that, in the second and third conditions for IFS(), we don’t need to ask the questions already answered by the failure of the previous conditions. This then gives you:

IFS(
  [Start Time] < "12:00:00",
    “Morning (8AM-Noon)”,
  [Start Time] < "16:00:00",
    “Midday (Noon-4PM)”,
  [Start Time] < "23:59:59",
    “Evening (4PM-8PM)”,
)

The last condition asks, is the start time before 23:59? Realistically, you probably just mean for this condition to catch anything not caught by the first two conditions. If so, you really don’t need a condition at all, you just need a way to catch everything:

IFS(
  [Start Time] < "12:00:00",
    “Morning (8AM-Noon)”,
  [Start Time] < "16:00:00",
    “Midday (Noon-4PM)”,
  TRUE,
    “Evening (4PM-8PM)”,
)

View solution in original post

EightyOsBen

EDIT:

Fixed this. I think I saw the trees but missed the forest on this one. Looks like the time column automatically does stay as time as long as only the TIME() function is used.

In other words, I didn’t need to extract the hour by using the HOUR() function and was able to get very specific on cutoff times (i.e. 11:59:00 as the upper limit for an AM shift).

Solution below and I’m happy to close this topic, but figured I’d leave it in case anybody else runs into a similar issue.

IFS(
TIME([Start Time])<TIME(12:00:00), “Morning (8AM-Noon)”,
AND(
TIME([Start Time])>TIME(11:59:59), TIME([Start Time])<TIME(16:00:00)), “Midday (Noon-4PM)”,
AND(
TIME([Start Time])>TIME(15:59:59), TIME([Start Time])<TIME(23:59:59)), “Evening (4PM-8PM)”,
)

Steve