Forum Discussion
Solved
interesting Booking template with multiple variants in Excel
Hello guys,
this topic is for those of you who want to push their creativity a bit further with excel. I've tried to make this example as easier as possible with all the respect for your time and effort. The final version will be more complex. I attach the example excel file in this message. It contains all the information.
I'm working on an booking and reservation calendar for my company. I'm stuck on the main part of this excel file: to create an formula that can mark in the calendar all the given dates with the apropriate reservation information in an 12 moths period (from 01.01.2022 to 31.12.2022). No more than 12 months.
The imput is:
floor / room / start date / end date / other info regarding reservation
The output must be:
color all the booked days in the calendar and display all the other information next to the colored cell
*each floor and each rooms are unique, there can't be duplicates
***the main problem here is when an booking ends today, 12.04.2022 and the next booking starts today, 12.04.2022. Should I split the cell that needs to color in 2 parts? two halfs meaning one full day? It does not need to count hours. Only days.
Is it possible do display the booking info as a note in a cell? when hold the mouse over the cell to display the content.
I've tried all kind of functions (SUMPRODUCT, IF, VLOOKUP, MATCH and index, arrays, etc) but none works and my knowledge is not enough to get trough this. Can you please help me? at least with an ideea of how could I make this work.
Many thanks in advance,
Alex
Alecs Not convinced that your template is the most effective way to approach this, but if it works for you, it's good enough. Played around a bit and added conditional formatting to display the status of the booking, and a FILTER function to pick-up the INFO on the first day of the booking. And it also deals with multiple bookings on one day.
See attached.
Alecs Not convinced that your template is the most effective way to approach this, but if it works for you, it's good enough. Played around a bit and added conditional formatting to display the status of the booking, and a FILTER function to pick-up the INFO on the first day of the booking. And it also deals with multiple bookings on one day.
See attached.
@Riny_van_Eekelen
Thank you very much for your efforts. It's perfect!!! just what I've been looking for.What do you mean by not the most effective way? I'm asking because I'm open to new suggestions. I've tried to make it as simple as I could. Please share with me if you got an better ideea. At least in a few sentences.
question please: I've noticed how it deals with multiple bookings in one day. It is just like I've imagined it. It colors with orange the date that has 2 bookings in the same day but ONLY if one of them is confirmed (green) and the another one is not (red). I'm trying now to make it work when both bookings are confirmed (green) -> to display orange color on the day 1 of the second booking (because red means = not confirmed)
I've made an example and attatched the screeshot bellow to make it more clear. It was impossible to be done or you just forgot about this scenario? just asking.
Thank you
Alecs
Alecs Whenever I see a schedule with many colours, borders, merged cells and must use rather complicated conditional formatting formulae to achieve results that should be relatively easy, my gut feeling says it should be done in another way. Right now I haven't really thought about how, though I wonder why you can't use a regular calendar app that comes with e.g. Outlook and create a separate calendar for each room and display them side-by-side. But, no offence. If it works for you, that's great.
With regard to double bookings, the orange colour is just a marker that there is a double booking that day. The colour directly above it tells you is the booking that ends that day was confirmed or not, as the colour directly below it tells you the status of the booking that starts that day. Obviously, if you have a booking for the morning only and another for the afternoon only, you can not show different colours and infos for both of them in one cell.
And that's probably where the design of the schedule fails. You should allow for booking partial days to avoid overlapping, if that's a real problem. So, split each day in at least two cells (AM, PM). But that might only complicate the matter, I think, when it comes to all the required formatting.
- I’d do it in steps. The last step is using ‘conditional formatting’ based on text in a cell.
For the first step, arranging the data, I’d use the Filter formula. And use multiple variables, referring to cells you can adjust as needed. Once you get everything working, use Sort combined, just for order. It might take some practising, so I’d start with a small test sheet.
Simple example: =Sort(Sheet1!A:G;Sheet1!A:A=A1*Sheet1!B:B=A2*Sheet!C:C=A3).- I understand. Yes, that's the way I've also thought about it. Looks like Riny gave me an amazing solution. I will continue to develop it forward. Thank you for your message. Much appreciated it.
