How to display time blocks correctly in calendar view based on enum selection

AppSheet AppSheet Q&A
,
1

Hi everyone,

I’m developing an app for managing parking spot reservations. Here’s the situation:

User Roles:

  • Priority Users: Can release their parking spots by selecting a date and a predefined time block.
  • Temporary Users: Can reserve available spots released by Priority Users.

Time Blocks: To simplify the process, Priority Users select from predefined time blocks instead of specific times:

  • Morning: 8:00 AM – 12:00 PM
  • Afternoon: 12:00 PM – 6:00 PM
  • Whole Day: 8:00 AM – 6:00 PM

I have an Enum column [Time Block] with these options.

The issue: I want the calendar view to display events in the correct time intervals based on the selected [Time Block], so Temporary Users can see available spots accurately in the calendar (not just as all-day events).

I’m having trouble calculating [Start Time] and [End Time] (both of type DateTime) based on the [Time Block] selection. I’ve tried the following expression in the [Start Time] column:

SWITCH( [Time Block], “Morning”, DATETIME(CONCATENATE(TEXT([Release Date], “YYYY-MM-DD”), " 08:00:00")), “Afternoon”, DATETIME(CONCATENATE(TEXT([Release Date], “YYYY-MM-DD”), " 12:00:00")), “Whole Day”, DATETIME(CONCATENATE(TEXT([Release Date], “YYYY-MM-DD”), " 08:00:00")), “” )

And similarly for [End Time], but with the appropriate end times. However, the [Start Time] and [End Time] aren’t calculating correctly, and events are not appearing in the calendar at the correct times—they either show as all-day events or don’t display in the hourly slots.

What I’ve checked:

  • [Release Date] is of type Date.
  • [Time Block] values match exactly (including capitalization and spelling).
  • [Start Time] and [End Time] are of type DateTime.
  • There are no errors shown in the expression tester.

Could someone please guide me on how to correctly calculate [Start Time] and [End Time] based on the [Time Block] selection, so that events display in the calendar view during the correct time intervals?

Any help would be greatly appreciated!

Thank you!

Barbara

2

Start Time would be:

(
  DATETIME([Release Date])
  + SWITCH(
    [Time Block],
    "Morning", "008:00:00",
    "Afternoon", "012:00:00",
    "Whole Day", "008:00:00")),
    ""
  )
)