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)”
TIME(HOUR(B2),0,0)<(12/24),“Morning (8 AM-Noon)”,
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.