Complex Logic to Filter Records Found within Start to End DateTime

[gymcode]

In my Gallery, I set the color of Desks that are available = Green, and Red for booked Desks, if active 'Reservation Details' records are found within selected Date.

Switch(
        LookUp(
            ForAll(
                Filter(
                    'Reservation Details',
                    Date >= FromDate_Dp.SelectedDate,
                    Date <= ToDate_Dp.SelectedDate && 'Booking Status' = 'Booking Status (Reservation Details)'.Booked
                ),
                {
                    DeskName: 'Reserved Desk'.Name,
                    BookingStatus: Text('Booking Status')
                }
            ),
            DeskName = ThisItem.Name
        ).BookingStatus,
        "Booked",
        Color.Red,
        Color.Green
    )

However, with 'Time' value implemented, I have to filter 'Reservation Details' via Date + Time.

What I have tried

Below is the code I tried to include Time, but the results are incorrect. 
(Booked Desks are still displayed as available in Green color)

Switch(
        LookUp(
            ForAll(
                Filter(
                    'Reservation Details',
                    'Start Date Time' >= FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0) &&
                    'End Date Time' <= ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0) && 'Booking Status' = 'Booking Status (Reservation Details)'.Booked
                ),
                {
                    DeskName: 'Reserved Desk'.Name,
                    BookingStatus: Text('Booking Status')
                }
            ),
            DeskName = ThisItem.Name
        ).BookingStatus,
        "Booked",
        Color.Red,
        Color.Green
    )

Sample Scenario for Modified Formula Above

Existing Reservations:

- 27 Feb 9am - 6pm

If user tries to book 27 Feb 12pm - 1pm, the first && condition will fulfil,

'Start Date Time' >= FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0)

but the second && condition below will be missed, so the Desk will still be shown as available.

 'End Date Time' <= ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0)

Further Analysis
Below are some permutations that I thought through. However, this logic will also wrongly block Desks if a different day is selected (e.g. 28 Feb).
*User is allowed to select multiple dates, but a fixed Start Time and End Time

Existing Reservation : 27 Feb 10am - 6pm

+ Permutations

1. Reservation 'Start Date Time' on/before Selected 'Start Date Time'
2. Reservation 'Start Date Time' on/after Selected 'End Date Time'

3. Reservation 'End Date Time' on/before Selected 'End Date Time'
4. Reservation 'End Date Time' on/after Selected 'End Date Time'


+ Scenarios to block Desk booking (would also wrongly block out Desks if a different day is selected, e.g. 28 Feb)

A) 27 Feb 12pm - 5pm
1. Reservation 'Start Date Time' on/before Selected 'Start Date Time'
3. Reservation 'End Date Time' on/before Selected 'End Date Time'

B) 27 Feb 9am - 5pm
2. Reservation 'Start Date Time' on/after Selected 'End Date Time'
4. Reservation 'End Date Time' on/after Selected 'End Date Time'

C) 27 Feb 12pm - 5pm
1. Reservation 'Start Date Time' on/before Selected 'Start Date Time'
4. Reservation 'End Date Time' on/after Selected 'End Date Time'

D) 27 Feb 9am - 6pm
2. Reservation 'Start Date Time' on/after Selected 'End Date Time'
3. Reservation 'End Date Time' on/before Selected 'End Date Time'

Any help is greatly appreciated.

[v-bofeng-msft]

Hi @gymcode ,

If you want to filter events based on the date selected by the user, you can try the following logic, meeting any of the following conditions:

• The start date falls between the user-selected start and end dates.
• The end date falls between the user-selected start and end dates.
• The start date is before the user-selected start date, and the end date is after the user-selected end date

('Start Date Time' >= FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0) &&
'Start Date Time'  < ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0)) ||
('End Date Time' > FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0) &&
'End Date Time' <= ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0)) ||
('Start Date Time' < FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0) &&
'End Date Time'  > ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0))

Best Regards,

Bof

[gymcode]

Hi @v-bofeng-msft , thank you for the formula.

I see that your suggested solution actually does cover the scenarios mentioned.

However, I encountered a troubling issue where the color is not toggled to Red even if the criteria are met.


Record Data: 22 Feb 12-6pm

User Selected DateTime: 22 Feb 12pm - 1pm
1. The start date falls between the user-selected start and end dates.
2. The end date falls between the user-selected start and end dates.

User Selected DateTime: 22 Feb 10-11am
3. The start date is before the user-selected start date, and the end date is after the user-selected end date

Below is my full formula for Gallery.Icon.Color

    Switch(
        LookUp(
            ForAll(
                Filter(
                    'Reservation Details',
                    (
                        ('Start Date Time' >= FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0) &&
                        'Start Date Time'  < ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0)) ||

                        ('End Date Time' > FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0) &&
                        'End Date Time' <= ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0)) ||

                        ('Start Date Time' < FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0) &&
                        'End Date Time'  > ToDate_Dp.SelectedDate + Time(Value(ToTimeHour_Ddl.Selected.Value), Value(ToTimeMinutes_Ddl.Selected.Value), 0))
                    )
                ),
                {
                    DeskName: 'Reserved Desk'.Name,
                    BookingStatus: Text('Booking Status')
                }
            ),
            DeskName = ThisItem.Name
        ).BookingStatus,
        "Booked",
        Color.Red,
        Color.Green
    )

[gymcode]

Hi @v-bofeng-msft, I also noticed a strange behavior when comparing datetime values.

With the exact start date (22 Feb 12pm) and user-selected start date (22 Feb 12pm), below validation does not take effect:

'Start Date Time' = FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0)

'Start Date Time' <= FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0)

However, when changed to >=, the validation takes effect.

'Start Date Time' >= FromDate_Dp.SelectedDate + Time(Value(FromTimeHour_Ddl.Selected.Value), Value(FromTimeMinutes_Ddl.Selected.Value), 0)

Is this an issue in PowerApps when comparing DateTime values?