Asset Reservation Solution

[CCJones]

I built an asset reservation system based on a solution provided by @WarrenBelz  in this post:
Solved: Asset availability - Power Platform Community (microsoft.com)

It works great with one exception that I can't seem to figure out. 

I think the original solution only relied on date values. Mine also uses time. Again, everything works perfectly except for this one thing. When an item that was reserved from, for example, 15 May 8:00 AM to 15 May 4:00 PM then has a reservation attempt for 15 May 10:00 AM to 15 May 2:00 PM, BETWEEN the check out and due date/times, the device appears as available in the dropdown. 

This is the code I used for the items in the dropdown control:

With(
    {
        wReserve: Sort(
            VivariumAssetReservations,
            ID,
            SortOrder.Descending
        ),
        wAsset: Sort(
            Filter(
                'Vivarium Assets',
                Status = "Available"
            ),
            ID,
            SortOrder.Descending
        ),
        wFrom: varReservationCheckOut,
        wTo: varReservationDue
    },
    Filter(
        wAsset,
        !(Device in Filter(
            wReserve,
            Device,
            ('Check Out Date' >= wFrom && 'Check Out Date' <= wTo) || ('Due Date' >= wFrom && 'Due Date' <= wTo)
        ).Device)
    )
)

This is the code to set the varReservationCheckOut variable in the OnChange of the check out date control and its time selectors (hours and minutes):

Set(
    varReservationCheckOut,
    If(
        Not IsBlank(ReservationCheckOutValue.SelectedDate),
        DateTime(
            Year(ReservationCheckOutValue.SelectedDate),
            Month(ReservationCheckOutValue.SelectedDate),
            Day(ReservationCheckOutValue.SelectedDate),
            Value(ResOutHourValue.Selected.Value),
            Value(ResOutMinuteValue.Selected.Value),
            0
        )
    )
)

And this is the code to set the varReservationDue variable in the OnChange of its controls:

Set(
    varReservationDue,
    If(
        Not IsBlank(ReservationDueValue.SelectedDate),
        DateTime(
            Year(ReservationDueValue.SelectedDate),
            Month(ReservationDueValue.SelectedDate),
            Day(ReservationDueValue.SelectedDate),
            Value(ResDueHourValue.Selected.Value),
            Value(ResDueMinuteValue.Selected.Value),
            0
        )
    )
)

As seen in the screenshot below, the device is reserved during the times the new reservation is attempted but still appears in the dropdown.

As shown below, it is only when the Check Out and Due Date/Times fall BETWEEN existing reservation date/times that the formula is returning devices that should not be returned.  I can overlap or coincide the date/times exactly and the device is not included in the dropdown. 

Looking at the logic in the formula, it seems as if it ought to work for date/times that fall between any existing reservation times. 

What am I missing?

[CCJones]

Writing it out, I see that the logic does not cover values that fall between the existing check out and due dates. 

existing check out date (5/15 8:00 AM) is greater than or equal to the requested check out date (5/15 9:00 AM)

FALSE
AND
existing check out date (5/15 8:00 AM) is less than or equal to the requested due date (5/15 2:00 PM) 

TRUE

FAIL

OR

existing due date (5/15 4:00 PM) is greater than or equal to the requested check out date (5/15 9:00 AM)

TRUE
AND
existing due date (5/15 4:00 PM) is less than or equal to the requested due date (5/15 2:00 PM)

FALSE

FAIL

Overall FAIL so device appears in dropdown.

I am terrible figuring these out. How should it be written to account for dates between existing check out and due dates?

[CCJones · Accepted solution]

I think I have it. Updated the filter formula as shown below and that seems to have done the trick.

Filter(
	wAsset,
	!(Device in Filter(
		wReserve,
		Device,
		('Check Out Date' >= wFrom && 'Check Out Date' <= wTo) 
        || 
        ('Due Date' >= wFrom && 'Due Date' <= wTo) 
        || 
        ('Check Out Date' <= wFrom && 'Due Date' >= wTo)
	).Device)
)

[WarrenBelz]

@CCJones is correct - that is an old post of mine and you will see the third option in the more recent examples. I have updated that post for the benefit of future enquiries.