Hotel booking engine module in Excel

[smittal]

Hello,


I am creating a booking engine module for a hotel website. As every hotel has defined number of rooms with defined room category, the concern is in checking the availability of a particular room type for a particular date.


The purpose of the module is that if a user enters arrival date, number of nights to stay in a hotel and room; how can this be checked in backend that room with a particular category is available or not.


https://www.dropbox.com/s/xqf5pi0j00y43oc/Forums Sharing Sheet.xlsm


Enclosed is the front end file for the same.


Thanks in advance


Any help will be really appreciate in same.


Regards,

Shweta

 

[Luke M]

I can't access files myself, but spit-balling an idea...


First, you're going to need to check a bunch of dates for overlap. I'd start with the formula Chandoo describes here:

http://chandoo.org/wp/2010/06/01/date-overlap-formulas/


Let's say your raw data in in col A:C, and looks like this:

Check-In    Check-Out    Type
1-Jan-13    4-Jan-13    a
6-Jan-13    6-Feb-13    a
11-Jan-13    12-Mar-13    a
16-Jan-13    4-Apr-13    a
21-Jan-13    4-May-13    a
26-Jan-13    14-Jun-13    a
31-Jan-13    6-Jul-13    a
5-Feb-13    4-Aug-13    a
10-Feb-13    10-Sep-13    a
15-Feb-13    4-Oct-13    a
20-Feb-13    8-Nov-13    a
25-Feb-13    6-Dec-13    a
2-Mar-13    7-Jan-14    a
7-Mar-13    4-Feb-14    a
12-Mar-13    4-Jan-13    b
17-Mar-13    4-Feb-13    b
22-Mar-13    7-Mar-13    b
27-Mar-13    15-Apr-13    b
1-Apr-13    4-May-13    b
6-Apr-13    6-Jun-13    b
11-Apr-13    13-Jul-13    b
16-Apr-13    11-Aug-13    b
21-Apr-13    12-Sep-13    b
26-Apr-13    5-Oct-13    b
1-May-13    9-Nov-13    b
6-May-13    6-Dec-13    b
11-May-13    4-Jan-14    b
16-May-13    12-Feb-14    b

Formula to count how many overlapping bookings is:

=COUNTIFS(B2:B29,">"&DesireCheckIn,A2:A29,"<"&DesireCheckOut,C2:C29,RoomType)

If you then take that and do a quick comparison to your total rooms per category (assume it's in a table somewhere, final formula would be something like:

=IF(COUNTIFS(B2:B29,">"&DesireCheckIn,A2:A29,"<"&DesireCheckOut,C2:C29,RoomType)<LOOKUP(RoomType,TotalRoomTable),"Book it!","We're full, sorry!")

 

[smittal]

Hello Luke,


i am re-posting the link, please check if this is working..


https://www.dropbox.com/s/dj259dogqym52ld/Forums Sharing Sheet.xlsm

 

[Luke M]

Sorry smittal, it's a problem on my end (your link is fine) I can't access any uploaded files. Hopefully someone else can come along and finish the work, or I was hoping my two cents would help you a little. Sorry about confusion.

 

[smittal]

Perfect Luke... Thank you so much...Kindly suggest one more thing.. just add No of Rooms also..

Arrival        Departure        Room type               no of rooms
17-Jul-13      18-july-2013     Standard single             4
17-Jul-13      18-july-2013     standard single             5
17-Jul-13      19-july-2013     deluxe double               2

suggest the formula in that case when person book more than 1 rooms


Thanks in advance for same.

 

[smittal]

sorry, i have done editing 3 times bt still its looks unreadable...

 

[smittal]

The no of rooms column is <Room booked by customer>

 

[Luke M]

Since we now need to sum up all the rooms in col D, we'll change to using a SUMIFS function, and change the logic check a little

=IF(SUMIFS(D2:D29,B2:B29,">"&DesireCheckIn,A2:A29,"<"&DesireCheckOut,C2:C29,RoomType)+Desired#OfRooms<LOOKUP(RoomType,TotalRoomTable),"Book it!","We're full, sorry!")

 

[smittal]

Hello Luke!!


Sorry for delay...formula suggested by you works perfect ... but just a slight confusion here.. should we use "<" function with lookup condition..or "<=" let eg,

Arrival          Departure          Room type          Rooms

20-Jul-13    21-Jul-13    Standard single    1

16-Jul-13    18-Jul-13    deluxe double    2

18-Jul-13    21-Jul-13    Deluxe Single    3

28-Jul-13    30-Jul-13    Standard double    2

15-Jul-13    16-Jul-13    suite single    1

22-Jul-13    26-Jul-13    family    4

15-Jul-13    18-Jul-13    suite double    3

15-Jul-13    17-Jul-13    Standard single    2

27-Jul-13    1-Aug-13    deluxe double    4

21-Jul-13    23-Jul-13    Deluxe Single    2

26-Jul-13    27-Jul-13    Standard double    1

24-Jul-13    25-Jul-13    suite single    1

24-Jul-13    25-Jul-13    family    1

27-Jul-13    28-Jul-13    suite double    1

27-Jul-13      5-Aug-13    Standard single    5

Let Say, whenever we take bookings, we want


Arrival Dt Depart Dt Room Category Room Required

20-Jul-13 21-Jul-13 Standard double 5


Now using this condition i got "No Rooms are Available!" message


=IF(SUMIFS(D:D,B:B,">"&F2,A:A,"<"&G2,C:C,"H2")+I2<VLOOKUP(H2,M:N,2,0),"Book it!","No Rooms are Available!")


This is the total room availability chart which shows we have 5 rooms for every category


Room Type No of Rooms

Standard single 5

deluxe double 5

Deluxe Single 5

Standard double 5

suite single 5

family 5

suite double 5


Is there is a miss in my understanding or we should use "<=" criteria

 

[smittal]

Thanks to all. Problem solved.

 

[smittal]

Hello All,

Suddenly there is a new challenge comes in same concept.... Please confirm should i continue with the same thread or need to create a new one.

Problem explanation is: - Now we want to watch the entire Month Booking e.g. From Month start Date to end date.

i had tried below mention formula but there is a logical Error.... i cant find the same..

=COUNTIFS(<Date of Departure>,"<"&<last day of the month>,<date of arrival>,">"&<first day of the Month>).. But what will be with that cond where <DOA> is Aug and <DOD> is sep... file uploaded for Reference

 

[NARAYANK991]

Hi Shweta ,

I am not able to understand your requirement ; however , if you are looking for formulae in the range N5:S34 in your worksheet , then try this :

=SUMIFS($I$4:$I$1229,$E$4:$E$1229,N$3&"*",$F$4:$F$1229,"<="&$M5,$H$4:$H$1229,">="&$M5)

Copy this across and down.

If you are looking for a general formula to cater to booking quantity , it will be :

=SUMIFS(No_of_Rooms,Room_Type,"Deluxe*",Date_of_Arrival,"<="&Last_Date_of_Period,Date_of_Departure,">="&First_Date_of_Period)

Instead of Deluxe , you can substitute any other room type.

It is assumed that the Date_of_Departure will always be later than the Date_of_Arrival.

Narayan

 

[smittal]

Sorry Narayan Sir.. for confusing Statement... i got my result in your first formula.

Actually My basic motive was to know the availability of vacate Room at any day of the Month.

Many Thanks for the same.