SOLVED

how to prevent double booking of room in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2026371%22%20slang%3D%22en-US%22%3Ehow%20to%20prevent%20double%20booking%20of%20room%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ei%20want%20to%20make%20room%20reservation%20in%20excel.%20But%20I%20could%20not%20find%20out%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Ehow%20to%20prevent%20double%20booking%3CBR%20%2F%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3EI%20want%20to%20be%20warned%20when%20I%20enter%20a%20date%20range%20that%20I%20have%20already%20given%20for%20the%20same%20room%2C%20with%20data%20validation!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECan%20anyone%20help%20me%20please%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2026371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026450%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20double%20booking%20of%20room%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026450%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F915699%22%20target%3D%22_blank%22%3E%40murre34%3C%2FA%3E%26nbsp%3BI%20suspect%20that%20your%20real%20life%20situation%20is%20more%20complicated%20than%20just%20a%20few%20rooms%20and%20that%20you%20allow%20check-in%20in%20the%20afternoon%20when%20a%20guest%20checked-out%20in%20the%20morning.%20The%20attached%20workbook%20contains%20an%20approach%20you%20might%20find%20helpful.%20The%20top%20part%20contains%20dynamic%20array%20formulae.%20Your%20Excel%20version%20might%20not%20support%20these.%20Therefore%2C%20I%20have%20a%20more%20traditional%20approach%20in%20the%20bottom%20part%20of%20the%20schedule.%20Note%20that%20I%20have%20used%20named%20ranges%20in%20both%20methods.%20Cells%20with%20blue-is%20colour%20require%20user%20input.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECounting%20double%20bookings%2C%20I%20believe%2C%20is%20easiest%20by%20%22calendarising%22%20a%20reservation%20day-by-day%20(0%20%3D%20free%2C%201%20%3D%20booked)%20and%20use%20SUMIF%20to%20add%20up%20the%20bookings%20per%20room%20per%20part%20of%20the%20day%20(Morning%20and%20Afternoon).%20Anything%20greater%20than%201%20indicates%20a%20double%20booking.%20This%20can%20then%20be%20coloured%20using%20CF.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumns%20E%3AF%20(unhide%2Fhid%20by%20pressing%20the%20%2B%20or%20-%20button%20above%20column%20G)%20are%20helper%20columns%20that%20add%20the%20time%20portion%20to%20the%20IN%20and%20OUT%20dates.%20Formulae%20need%20to%20be%20copied%20down%20when%20inserting%2Fadding%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20all%20of%20this%20is%20just%20meant%20to%20see%20if%20this%20is%20something%20you%20could%20work%20with.%20It%20could%20be%20made%20to%20look%20a%20lot%20prettier%2C%20but%20that%20would%20require%20more%20effort%2C%20which%20I%20believe%2C%20goes%20beyond%20the%20scope%20of%20this%20forum.%20Moreover%2C%20if%20you%20are%20in%20the%20hotel%20business%2C%20I%20guess%20there%20must%20be%20off-the%20shelf%20booking%20systems%20that%20do%20all%20you%20need%20automatically%2C%20without%20the%20need%20to%20re-invent%20the%20wheel%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2027423%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20prevent%20double%20booking%20of%20room%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027423%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bi%20am%20very%20grateful%20for%20your%20support%2C%20i%20am%20sorry%20that%20i%20couldn't%20respond%20your%20message%20earlier.%20I%20will%20check%20it%20out%20your%20file%20and%20will%20try%20to%26nbsp%3B%20adapt%20to%20my%20system.%20I%20have%20excel%202016.%20Do%20you%20mind%20if%20i%20asking%20you%20questions%20if%20i%20have%20a%20problem%20with%20these%20formulas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 range that I have already given for the same room, with data validation!
Can anyone help me please

4 Replies
best response confirmed by murre34 (New Contributor)
Solution

@murre34 I suspect that your real life situation is more complicated than just a few rooms and that you allow check-in in the afternoon when a guest checked-out in the morning. The attached workbook contains an approach you might find helpful. The top part contains dynamic array formulae. Your Excel version might not support these. Therefore, I have a more traditional approach in the bottom part of the schedule. Note that I have used named ranges in both methods. Cells with blue-is colour require user input.

 

Counting double bookings, I believe, is easiest by "calendarising" a reservation day-by-day (0 = free, 1 = booked) and use SUMIF to add up the bookings per room per part of the day (Morning and Afternoon). Anything greater than 1 indicates a double booking. This can then be coloured using CF.

 

Columns E:F (unhide/hid by pressing the + or - button above column G) are helper columns that add the time portion to the IN and OUT dates. Formulae need to be copied down when inserting/adding rows. 

 

Now, all of this is just meant to see if this is something you could work with. It could be made to look a lot prettier, but that would require more effort, which I believe, goes beyond the scope of this forum. Moreover, if you are in the hotel business, I guess there must be off-the shelf booking systems that do all you need automatically, without the need to re-invent the wheel in Excel.

@Riny_van_Eekelen i am very grateful for your support, i am sorry that i couldn't respond your message earlier. I will check it out your file and will try to  adapt to my system. I have excel 2016. Do you mind if i asking you questions if i have a problem with these formulas?

@murre34 let me know if you any more questions. 

@murre34 

I did have a quick look and start sketching something out using MS365; no good to you I see.

One thing that I did implement was to allow the free entry of dates but then to control the assignment of rooms to those still available for the entire period of the booking with data validation.