Converting datetime to time and filtering based on...

drlchurch · 03-30-2021 10:17 AM

I have an app with columns that have a datetime value. I want to make a chart that shows the count of rows based on time of day as defined “Morning” (0600-1400), “Afternoon”(1400-2200), and “Night”(2200-0600). To do this I have tried creating a virtual column for the time of day that will hold these text values, but I cannot seem to get the formula right to create them. I have tried the following
SWITCH(TIME([Date & Time]),
<=06:00:00 && >14:00:00, “Morning”,
<=14:00:00 &&>22:00:00, “Afternoon”,
<=22:00:00 &&>23:59:59, “Night”,
“Night”)
This gives an error “The given key was not present in the dictionary…”

I have also tried
IFS(TIME([Date & Time])>=06:00:00, “Morning”,
TIME([Date & Time])>=14:00:00, “Afternoon”,
TIME([Date & Time])>=22:00:00, “Night”,
TIME([Date & Time])<06:00:00, “Night”)
This gives an error “Arithmetic expression ‘(TIME([Date & Time]) >= “06:00:00”)’ does not have valid input types”

I have tried various other combinations of these expressions, always with similar errors. Is there another way to do this?

Thanks,
Matt

Steve

In the first expression, you misused SWITCH(), and AppSheet has no && operator.

What is the column type of the Date & Time column?

See also:

drlchurch

The [Date & Time] column is type DateTime. I am trying to get a time only value to create this time of day chart. I was expecting SWITCH() to evaluate the time value against the range of values defined by the && (or the AND() as you pointed out). From the sounds of it this is not possible with the SWITCH() function?

In which case I believe IFS() would be more appropriate. I believe that the above expression would evaluate as such. Given a value from the [Date & Time] column, say 03/31/2021 07:26:00. TIME([03/31/2021 07:26:00]) would evaluate as 07:26:00, then check the value against the IFS statements. >= 06:00:00 would return “Morning”.

Steve

Please read the help doc linked above.

drlchurch

I have read the above doc many times before I made my original post. I am having a fundamental difficulty understanding it and why it is not evaluating correctly. Is the SWITCH() function looking for specific values, or is it possible to evaluate it with a range of values? It seems to me like it is looking for a specific value, in which case it can not be used in this scope as it would be prohibitive to type every value between 00:00:00 and 23:59:59 as there would be 86,400 matched pair statements. If it is able to check a range of values there must be some other way for me to express that range that I am just not understanding.

What I would like to do with the switch function
SWITCH(TIME([Date & Time]), //Get the time value (some value) to compare
“expected value1”, “Morning”, // expected value 1 would be between 06:00:00 and 13:59:59
“expected value2”, “Evening”, // expected value2 would be between 14:00:00 and 21:59:59
“Night”) // all other values would be evaluated as night

It seems I do not know how to express the range of values for expected value1 and expected value2 (if this is even possible), but I do think I understand the syntax of SWITCH().

Steve

The document is pretty clear on this question. A range is not a value.

drlchurch

I have determined the issue with my IFS statement. I did not realize TIME() returned a text value and per your info in the HOUR() article used this function to convert it to a number and evaluate my expression correctly. Thank you for the help.

GGL

Hi, I have the exact same requirement but I don’t quite understand the revelation you alluded to in your final post. Is there any chance of you posting your working solution? Thank you!

drlchurch

IFS(
AND(HOUR(TIME([Date & Time])-“00:00:00”)>=6, HOUR(TIME([Date & Time])-“00:00:00”)<14), “Morning”,
AND(HOUR(TIME([Date & Time])-“00:00:00”)>=14, HOUR(TIME([Date & Time])-“00:00:00”)<22), “Afternoon”,
AND(HOUR(TIME([Date & Time])-“00:00:00”)>=22, HOUR(TIME([Date & Time])-“00:00:00”)<24), “Night”,
AND(HOUR(TIME([Date & Time])-“00:00:00”)>=0, HOUR(TIME([Date & Time])-“00:00:00”)<6), “Night”)

drlchurch

So my understanding is the TIME([Date & Time]) column returns a text value. The HOUR() function is looking for a number. By subtracting 00:00:00 from TIME([Date & Time]) it converts it back into a number of appropriate format to be compared with HOUR(). The HOUR(TIME([Time & Date]) - “00:00:00”) returns a number 0 through 24. I compared the value returned here to my defined range with the AND() function for instance the first line evaluates that to be true it needs to be both greater than or equal to 6 and also less than 14. Lastly I plugged my defined ranges into IFS(). IFS(range 1, result 1, range 2, result 2, etc.) where the ranges were the AND() statements as described above and the results were text values I wanted to show in the virtual column.

Steve

TIME() returns a Time value, which is best enclosed in quotes when entered literally in an expression.

HOUR() expects a Duration value and returns a Number value.

Subtracting a Time value (00:00:00) from another Time value (TIME([Date & Time])) produces a Duration value, which is what HOUR() expects to get.

Converting datetime to time and filtering based on time of day