Generate List of Unrecorded time ranges

[chiukim]

here is my appsheet columns

usage_id - key column
time_from and time_to columns with time column type
travel_date column with a date column type
vehicle_status column with enum type column with (“OPERATION”, “TRAVEL”) values

what if i have this data
usage_id - 1
time_from - 08:50 am
time_to - 9:30 am
travel_date - 03/14/2025
vehicle_status - operation

usage_id - 2
time_from - 01:50 pm
time_to - 3:30 pm
travel_date - 03/14/2025
vehicle_status - travel

how can i return a set of list values with time type that all time range not encoded from 8:00 am to 5:00 pm per day
expected output :
08:00 am - 08:49 am, 09:31 am - 12:00 pm, 01:00 pm - 01:49 pm, 03:31 pm - 05:00 pm

[TeeSee1]

Here is one idea to do this.

Result:

Formulae used:

I did not implement a solution for a gap that starts at 8:00 but if you like the idea, you should be able to extend it to include that part on your own.