Availability Check

[Nathalie_Collin]

Hi All,

I’m banging my head up against the wall trying to figure this one out. Hopefully someone out there will be able to help. I have a base that is similar to a hotel or airbnb. So there are bookings, checkins etc.

When someone calls in to see if there is any availability for a specific date with a specific room type, there needs to be some sort of quick check. A Check Availability sheet was created whereby an admin enters in preferred room type along with required start and end dates. The record then has a field that checks all bookings and returns a number value for all available suites.

All seemed well until I realized that it wasn’t working. We were getting false positives. It’s hard to explain but basically, if either the start date or the end date was listed in the booking, then it returns booked. The formulation wasn’t taking into account the dates before and after. The scenario is like this;

Assume that there are only 2 rooms in the hotel and assume that there are only 3 bookings so far.
Room 1 - Booking 20190109-20190113
Room 1 - Booking 20190117-20190121
Room 2 - Booking 20190102-20190112

Someone calls and asks if there is any availability from 20190102 to 20190103. The system will return;
Room 1 - Available
Room 2 - BOOKED
~The reason is that 20190102 is listed under Room 2

Someone calls and asks if there is any availability from 20190101 to 20190131. The system will return;
Room 1 - Available
Room 2 - Available
~Both are coming up as Available, even though both rooms are booked for a portion of the month. The result should be BOOKED for both rooms.

I did try to do a workaround by pulling everything into a calendar, for a visual check, but it was way too busy for that to even be considered.

Would love to hear your ideas, thoughts and suggestions.
Thanks in advance,
Nathalie

[bdelanghe]

Hi @Nathalie_Collins,

I see two different possibilities the first is to make a formula field to check against all the records:

IF(OR(
  AND({check_start} >= {booking_start},
      {check_start} <= {booking_end}),
  AND({check_end} >= {booking_start},
      {check_start} <= {booking_end})),
  'BOOKED','Available')

Essentially if either the start or end of an availability check then they overlap.

The second option is to create booking slots for each day rather than using a start and end date. This has a few benefits, but it’s tedious to enter. I’ll share my base which has both examples:

Airtable

Availability Check - Airtable

Explore the "Availability Check" base on Airtable.

[Nathalie_Collin]

Thanks for the quick reply! I’ll look at this in greater depth and circle back.

[Ken_Baechtold]

I was trying this for booking services, and I think mine isn’t as complicated but I’m not sure where I’m going wrong with it. @bdelanghe, would you be able to help me as well? We have check-in and check-outs for our boarding kennel. We just want it to show “Available” or “Booked” based on the check-in time. If the check-in date is blank, we want that specific kennel to show “Available”. If check-in isn’t blank, we want it to show “booked”. Is there a way to do this easily with formulas?

[Nathalie_Collin]

Hi @Ken_Baechtold and Welcome to Airtable Community :smiling_face_with_three_hearts:

This should work --> IF({Check-in Date}="","Available","Booked")

I’m ‘pre’ coffee so no guarantees :laughing:

[W_Vann_Hall]

I’d recommend

IF(
    NOT(
        {Check-in Date}
        ),
    "Available",
    "Booked"
    )

That’s gets around the occasional issues one can have when BLANK() != '' by letting Airtable decide the appropriate test for a null value…

[Ken_Baechtold]

Nathalie_Collins:

IF({Check-in Date}="",“Available”,“Booked”)

That worked. Thank you!