Multiple if conditions in Search box (greater than, less than simultaneously)


I am building a hotel-management app.

Have run into a problem, and have spent a lot of time on it and tried different approaches but none have worked so far.

Problem Summary:;

I have 2 date-time pickers on the home page (for admin view) to view the number of booked nights for reservations that are attributed to a property (property has a date type room; and the room contains the reservation data type as one of its fields).

Each reservation has a check in date and a check out date (the date format for reservation is “time and date”) on the basis of which “booked nights” is calculated. Booked nights is basically check out date - check in date.

How I would want it to perform:

If the admin/ user selects 1st march to 15th march and there is a booking in the selected property from 7th march to 20th march then the filtered view should show booked nights as 8 (i.e 7th to 15th night).

There are multiple conditions that need to work in order for the booked nights to appear correctly. I am showcasing the one which is giving inaccurate results due to double counting*.
Specifically, that I want condition B and C to be there in a single statement because if taken separately then the result is inaccurate (plz refer image).*

Where I am stuck specifically:

The above given case does not function properly because the check out date is required to be less than Date Time picker end value (red box) while also being greater than greater than Date Time picker start value (green box). Basically in a date-range. While the check-in date needs to be less than or equal to the Date Time picker start value (green box)

The correct function I wish to write:

I would want to merge B & C within one if statement.

P.S I have tried via date range as well but that was not that easy either. Hoping someone can help me with this peculiar problem

Solved this using DateRange function. Took a lot of effort and a tonne of thinking as well as sequential formulas as well.

1 Like