Hello I have a problem ,I should manage the a...

[Giuseppe_Elio_N]

Hello I have a problem ,I should manage the availability of 10 rooms, in the sheet I have the column type enum list [camare] (101-102 -… 110), the column type data [strat booking] and the column type data [end booking]. I would like to check the dates and rooms so that I appear “booked” if there is already a reservation for that room. how can I do? thank you

[RezaRaoofi]

You could make a Virtual Column with a formula containing IF(…) function that returns “Booked” value if the current date/time is between [strat booking] and [end booking].

You might also want to check out these articles: https://intercom.help/appsheet/data/columns/virtual-columns

https://intercom.help/appsheet/expressions/expression-types/other-expressions

[Giuseppe_Elio_N]

hi and thanks. I have already tried this, but the control is not just for [start booking] and [end booking] but also for [room].

=IF(AND([ROOM]=“101”,[START BOKING]>=[END BOOKING],[STRAT BOOKING]<=[END BOOKING]),“AVAILABLE”,“NOT AVAILABLE”)

but not work.

[Giuseppe_Elio_N]

Thank you so much,you are the best…now is perfect…

[RezaRaoofi]

I believe you need to compare those

[strat booking] and [end booking] columns with the current time or the time user is entering to check for availability, and check to see if that time is between [strat booking] and [end booking].

[Giuseppe_Elio_N]

hi, I tried but it does not work. other ideas?

[RezaRaoofi]

Can you provide more details? Perhaps screenshot of column structure, and an explanation of how you have defined the booking logic in your system.

[Giuseppe_Elio_N]

hello

the logic of the system is this: in appashhet I choose the room, I enter the date of strat booking and booking end date. I would like an error if the room is already booked.

[Giuseppe_Elio_N]

+Steve Coile can you help me?thanks

[Giuseppe_Elio_N]

Thank you so much +Steve Coile

The function is perfect but only small favor.I use it for bookings of boats and not for room. the final day of booking the boat must not be available.

example: if the booking is from 22/12 to 23/12 on 23 the boat is not available for all day. the day will be available on 24. I tried to change your formula but I did not succeed.

Thanks again for the help

[Steven_Coile]

Change this:

[Start Booking] >= [__THISROW].[End Booking]

to this:

[Start Booking] > [__THISROW].[End Booking]

(replace >= with >).

Similarly, change this:

[End Booking] <= [__THISROW].[Start Booking]

to this:

[End Booking] < [__THISROW].[Start Booking]

(replace <= with <).

Again, remove one of the two underscores in front of THISROW.

[Steven_Coile]

Create a virtual column in the table containing the room bookings (I’ve assumed the table is called Bookings). Set the virtual column’s app formula to ="".

Once the virtual column is saved, change it’s type from Text to Show, set the Category to Text, and set the Content property to the following expression:

=IF(

(

COUNT(

FILTER(

“Bookings”,

AND(

([Room] = [__THISROW].[Room]),

NOT(

OR(

([Start Booking] >= [__THISROW].[End Booking]),

([End Booking] <= [__THISROW].[Start Booking])

)

)

)

)

) = 0

),

“AVAILABLE”,

“NOT AVAILABLE” )

[Start Booking] >= [__THISROW].[End Booking] chooses existing bookings that start on or after the new booking’s End Booking date.

[End Booking] <= [__THISROW].[Start Booking] chooses existing bookings that end before or on the new booking’s Start Booking date.

OR(…, …) chooses existing bookings that match either condition, effectively choosing all bookings that do not overlap with the new booking.

NOT(…) inverts the condition, effectively omitting all bookings that do not overlap with the new booking, leaving only bookings that do overlap.

[Room] = [__THISROW].[Room] chooses existing bookings for the same room as the new booking.

AND(…, …) requires that both conditions match, effectively choosing all bookings for the same room with dates that overlap the new booking.

FILTER(“Booking”", …) returns a list of existing bookings that overlap with the new booking.

COUNT(…) = 0 counts the number of bookings that overlap the new booking. No overlap indicates the room is available for the new booking.

=IF(…, “AVAILABLE”, “NOT AVAILABLE”) determines and indicates whether a room is or is not available for a new booking.

In the above, remove one of the two underscores before each THISROW. Google+ formatting sometimes gets confused by underscores.