How to prevent double room reservation Hi

[bmkucuk]

Hi,
i want to make room reservation in excel. But I could not find out how to prevent double booking

I want to be warned when I enter a date I have already given for the same room
Can anyone help me please

I cant VBA!!

[Limor_OP]

Hi,
please test this in E2 and down:
=IF(SUM((A2=$A$2:$A$6)*(C2>=$C$2:$C$6)*(D2<=$D$2:$D$6))-1,"double booking","OK to book")

[bmkucuk]

Hi,
please test this in E2 and down:
=IF(SUM((A2=$A$2:$A$6)*(C2>=$C$2:$C$6)*(D2<=$D$2:$D$6))-1,"double booking","OK to book")

thank for your quick support Belinde but it is not woking ! i add your code to the excel sheet and sendign as atachment

[Richard Buttrey]

We need to understand a bit more.

Is the list you show always in the order of bookings as they come in which will never be altered, or will it be sorted at any point by either room, name or date.

The solution will depend on understanding this. Essentially can we rely on all the the rows above a new booking always being in chronological order of the date they are entered to Excel(and I don't mean the check in date)?

[Limor_OP]

Hi,
1. You are right
2. Please remove the @ from the formula
3. please test this one:
=IF(SUM((A2=$A$2:$A$6)*(C2>=$C$2:$C$6)*(C2<$D$2:$D$6))-1,"double booking","ok to book")

[bmkucuk]

Belinda you are an angel THANK You VERY MUCH

[Limor_OP]

I suggest you do test it with several scenarios and pay attention to what @Richard Buttrey wrote...
Good Luck.

[bmkucuk]

We need to understand a bit more.

Is the list you show always in the order of bookings as they come in which will never be altered, or will it be sorted at any point by either room, name or date.

The solution will depend on understanding this. Essentially can we rely on all the the rows above a new booking always being in chronological order of the date they are entered to Excel(and I don't mean the check in date)?

I am sorry for my late response Richard, the answers to both of your questions is yes.

[bmkucuk]

Dear Belinda your formula is working at your excel sheet but when i copied to my excel sheet it does not working, can you tell me what am i doing wrong? (attached my file) !
at your excel shett show { } at beginnig and the of the formula but not on my sheet.
And one more question : how can i use this code as data validation under column c and d?
regards

[Limor_OP]

Belinda you are an angel THANK You VERY MUCH

Hi
I added the sign "=" to the formula in case the new guest arrives on same day the other is leaving and it considers double booking....

=IF(SUM((A2=$A$2:$A$6)*(C2>=$C$2:$C$6)*(C2=<$D$2:$D$6))-1,"double booking","ok to book")

[Limor_OP]

.............. it does not working, can you tell me what am i doing wrong? .........

Hi,
The formula is CSE type.
Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Or you can change the SUM to SUMPRODUCT:

=IF(SUMPRODUCT((A2=$A$2:$A$6)*(C2>=$C$2:$C$6)*(C2=<$D$2:$D$6))-1,"double booking","ok to book")

[Limor_OP]

......how can i use this code as data validation under column c and d?

what kind of a data validation you want in columns C and D ? Please explain.

[etaf]

C2=<$D$2:$D$6)

C2<=$D$2:$D$6)
Note the = needs to be to the right of a < or >

[Limor_OP]

You're right etaf
The excel auto corrects this syntax error...

[bmkucuk]

I want it to work with the same logic ! if there is a date range entered for the same room, it shouldn't allow it. Thank you)

[bmkucuk]

what kind of a data validation you want in columns C and D ? Please explain.

I want it to work with the same logic ! if there is a date range entered for the same room, it shouldn't allow it. Thank you

[kvsrinivasamurthy]

Use data --> validation so that you can prevent the double entry itself.
Select Range C2:C7
Data --> Validation
Custom
Enter the formula

=SUMPRODUCT(($A$1:$A1=$A2)*($C$1:$C1<=$C2)*($D$1:$D1>=$C2))=0

For range $D2:$D7 formula

=$C2<>""

This will not allow the dates in column C if it is already booked.
In Range D cannot be entered unless column C is filled. That is if C7 is vacant D7 cannot be filled.

[bmkucuk]

Thank you very much, smooth as silk, I am very grateful

[josephteh]

If the idea is to prevent double booking of rooms, isn't it better to check for availability date for each room?
E1 = "Availability Date"
E2 = IF(ISBLANK(A2),"",AGGREGATE(14,6,D$2:D2/(A$2:A2=A2),1)+1)

[bmkucuk]

Could you please put your formulation on my sample page and post here please?

[josephteh]

If the idea is to prevent double booking of rooms, isn't it better to check for availability date for each room?
E1 = "Availability Date"
E2 = IF(ISBLANK(A2),"",AGGREGATE(14,6,D$2:D2/(A$2:A2=A2),1)+1)

[bmkucuk]

If the idea is to prevent double booking of rooms, isn't it better to check for availability date for each room?
E1 = "Availability Date"
E2 = IF(ISBLANK(A2),"",AGGREGATE(14,6,D$2:D2/(A$2:A2=A2),1)+1)

Thank you for your support but when i have many entrances i have to check it out one by one, it is imposible to that
And it is not working anyway because it is calculate the checkout date.
room 11 booked fr 01/04 to 04/04
room 11 booked fr 06/04 to 09/04
accordind to this calculation first avaliable date is 10/04
but 05/04 is availbale too ! )

[josephteh]

So, you do allow for double-booking of rooms to maximise profits in case some guests do not turn up?

[bmkucuk]

So, you do allow for double-booking of rooms to maximise profits in case some guests do not turn up?

On the contrary, I have already asked for help not to do this and I have received what I wanted. If you check post numer 9 and 17 you can see it.

[josephteh]

Why do you need to check one by one? Once you key in the room number in column A, availability of the room would be shown in column E at the same row.

On the contrary, data validation only blocks you from entering non-available dates and does not tell you when the room is available.

[kvsrinivasamurthy]

Sorry for the mistake.
Formula I have given in post #17 for column D validation is not full proof. I have checked it. So the revised formula for Validation of Column D is

=AND($C2<>"",SUMPRODUCT(($A$1:$A1=$A2)*($C$1:$C1>$C2)*($C$1:$C1<=$D2))=0)

Pl change it.

[bmkucuk]

Why do you need to check one by one? ...

Please check this spreadsheet maybe you can understand what I have said before!
As you can see between 10 and 13 room 11 is available, but it shows 17 as the first available date
Because formula calculates the last check-out day! But there are available dates in between. What will happen if i sell room 11 1/5 today ???
You need to understand how a booking system works.

[bmkucuk]

Sorry for the mistake....

I noticed this too, but I didn't want to disturb you any more, thank you again.

[josephteh]

Thank you, bmkucuk. I understand now. Bookings for earlier dates can come in later.

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[bmkucuk]

I am sorry Ali

[bmkucuk]

Hi kvsrinivasamurthy,
if I remove the formula from data validation and then paste it again it does not work
Or if I put the formula on another page it does not work either.
But it works on your sheet, I'm doing something wrong but what?
Any Idea?

=AND($C2<>"";SUMPRODUCT(($A$1:$A1=$A2)*($C$1:$C1>$C2)*($C$1:$C1<=$D2))=0)

[kvsrinivasamurthy]

Validation works only when there is new entry in the cell. If data is already existing before applying validation it will not validate. If you can upload sample file it can checked.

[bmkucuk]

I am sending sample file.

[kvsrinivasamurthy]

I asked for sample file not so simple file. Ok . That is just for joke.
Pl see the uploaded file.
In the sheet Sayfa1 formula for data validation for Column D is

Please Login or Register  to view this content.

For Column C No change in Previous formula.
Data validation takes care of new entry only. If already existing rows are edited validation takes care of rows above only. This problem can be avoided by using worksheet event . Same is given in Sheet1.

Code :

Please Login or Register  to view this content.

Worksheetevent

To paste the code
Right click on Sheet tab --> view code
VB window opens.
Paste the code
Close the window.

[bmkucuk]

The god of Excel, I must bow to your superior knowledge, Thank you again,You are the one

[kvsrinivasamurthy]

Thanks for the feedback and good words.