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
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |