how to prevent double booking of room in excel

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.