Looking to create a boat rental booking sheet(s)

I am looking to create a boat rental reservation sheet that I can enter information into that would then transfer over to a calendar sheet.
Days would have to overlap as there would be 1/2 day, daily and weekly rentals up to 10 different boats.
Attached is a word document that we currently use to print and manually write into a book.
Problem is that this one book is never in the right place at the right time.
Looking to make something that we can use on tablets placed in different locations.
Not looking to buy a program but make something that will be easy to use.
Currently using Excel 2019.

Am I asking for the impossible?

I am not looking for someone to do the leg work just a few tips to get me going.

Thanks.

Hi and welcome. Interesting little project this but not enough info provided.

A few questions in the first instance:
Is the same file to be used on multiple tablets? If so how do you plan on sharing it?
What sort of tablets are they? Android / iOS / Windows? - The reason for this question is you could make a nice looking tool for this using VBA but that wouldn't be an option on Android or iOS.
Could someone hire a boat each morning on multiple days? i.e. I hire boat 1 on Monday morning and Tuesday morning but someone else has it Monday afternoon?

BSB

Originally Posted by BadlySpelledBuoy

Hi and welcome. Interesting little project this but not enough info provided.

A few questions in the first instance:
Is the same file to be used on multiple tablets? If so how do you plan on sharing it?
What sort of tablets are they? Android / iOS / Windows? - The reason for this question is you could make a nice looking tool for this using VBA but that wouldn't be an option on Android or iOS.
Could someone hire a boat each morning on multiple days? i.e. I hire boat 1 on Monday morning and Tuesday morning but someone else has it Monday afternoon?

BSB

Thanks for the response. Looking to use it on IOS tablets through Drop Box. I know right now I can share excel files and alter them on my Ipad.
They are just spread sheets though if that makes a difference.
So how this works is we rent out boats hourly, 1/2 day, daily and weekly. Our days are 10 hours 9-7 and half days are 9-2 or 2-7.
One single boat could be used more then once per day say from 9-2 and then out again from 2-7. Same for weekly eg. from 2pm on one Saturday and back the following Sat. a 2pm and then go back out for another week.
You are correct that 1 boat could be rented out Monday morning from 9-2 and then to the same person Tuesday morning 9-2 but also get rented out Monday afternoon from 2-7 by a different person.
Also again the same boat rented to a different person Tuesday afternoon from 2-7. Not that this happens a lot.
Rates are hourly, 1/2 day, daily and weekly. Obviously. No offence.
Is this a good explanation?

Thanks.

Thanks. That is kind of what I am looking for.
Could a date picker be used in the date columns?

Thank you. Will give it a shot.

Once you have experimented, and you have settled on a particular layout for your data then post it back and I'll see about displaying it as a calendar in another sheet.

Pete

This is pretty close to the layout. If you look at the spreadsheet you will see that I entered the same boat in every line with the same time period and date and there was not conflict.
Wondering why that is happening? There should be a conflict on the second line down as soon as I put in the same product for the same date and time.
Thanks in advance.

I've made the changes shown in red to the formula in H3:

=IF(SUMPRODUCT((B$2:B2=B3)*(D$2:D2=D3)*(F$2:F2<=F3)*(G$2:G2>=G3)*(B3<>"")),TRUE,"")

then copied it down. The = sign in the term for column F was causing the problem, but I also added the final term to ensure that blank rows were not flagged up.

Hope this helps.

Pete

P.S. Regarding the calendar, do you have a preferred layout? I imagine you would want a month-to-view, with the month and year selectable via drop-downs. This could be Sunday to Saturday across the screen, with up to 5 weeks shown for the month (any "orphan" days would appear in the first week). You mentioned having 10 boats, so presumably you would want to have 10 rows for each week, and for each day to be split into AM and PM bookings. An hourly booking would have to show the start time to distinguish it. Presumably you would want to show the booker's name. Do you record a contact number for the Booker?

Please supply a list of the names you use for each boat, along with a more-representative sample of booking data.

Pete

You are correct with the calendar layout. Could there be different colours for each booking so as to see them easier at a glance on the calendar? IE. Blue boat would have maybe blue across a whole week and then Red boat would show red. Might make it easier to differentiate between a bunch of rentals. Just a thought. Contact number and name would be required.
My first post has a word document attached for what we use now as a booking sheet for more detail.

Example would be: John Doe 705-555-5555 Lund 25hp (White) Picking up at 2pm Drop off 2pm From Sat. July 3rd - Sat. July 10th.

Here is the boat list.

Lund 25hp (White)
Lund 25hp (Black)
Lund 25hp (Blue)
Lund 25hp (Red)
Larson 115hp Yamaha
Seaswirl 4.3 L
Bennington 115hp Pontoon
Lund 60hp (Fishing Boat)
Springbok 40hp
Barge

Thanks again.

I am trying to adjust the sheet. Lets say I have a rental for a week but it is pick-up at 2:00pm and return a week later at 2:00pm or pick-up at 2:00pm and return 7:00pm a week later or even pick-up at 9:00am return 2:00pm. If I adjust anything it throws off the formulas. Even a 6 day rental instead of a 7 day rental. Would I have to update the list with each scenario I might need? That would also mean adjusting the formula to include more in the lists and different times also correct?

I think in the first case that you outline you would enter it as a half-day PM rental on the first day, followed by a week rental for 6 days (using the end-date to specify this), and then another half-day rental for AM on the last day. The same type of approach could be used for the other scenarios. I wasn't envisaging having many formulae within the data-entry sheet, in order to give you some flexibility. You might have a booking for half-day only but over several days, for example, but that can be catered for using the date range.

I was working on this yesterday, and I know that you said in your first post that you were just looking for some tips, but I think it is easier if I develop the calendar for you and then just explain the main parts of it. The thing that has stumped me at the moment is how to deal with hourly bookings, because if someone books a boat for say 9:00am for an hour, then there is the possibility of further bookings in that AM slot, but for the other booking types there would only be one entry. I'm not sure how best to deal with this as the moment, but I'll plod on and it will come to me eventually.

Hope this helps.

Pete

Thanks Pete. I was not expecting someone to develop something but I see what you mean. It gets to complicated to explain and much easier to just do it. We do not get too many hourly bookings mostly half day, daily and weekly.
A lot of people that are renting cottages on the lake will not usually pick up the boat until 2:00pm say on a Saturday and then return the following Saturday by 2:00pm. That would be their 7 days. Or the other scenario say for 7 days would be pick up at 9:00am on Sat. and drop back off at 7:00pm the next Friday. I can always figure out something for hourly on a separate worksheet.

Thanks again.

Rod

Just to let you know that I'm still working on the calendar display off and on, so I haven't forgotten about it. In setting this up I've concluded that "Weekly" is not really a rental type, as the duration of the booking can be specified by the Start Date and End Date. (I appreciate that you might have a different charging rate for Weekly, but this isn't about working out the charges). The rental types are thus: Half Day AM, Half Day PM, Full Day, and Hourly. With an Hourly booking, I shall show the Start Time in front of the Name and Contact Number., whereas the other rental types will show just the Name and Contact No. As discussed earlier, if you have more than one hourly booking in an AM or PM slot, only the first (recorded) one can be displayed.

Each day of the calendar will be divided in two, for AM (9:00-14:00 bookings) and for PM (14:00-19:00 bookings), and Full Day bookings will span both. You can specify a Start Date and End Date (optional for one-day bookings) for each booking type, and the display will cover this duration automatically. The boats will be listed in the same relative position for each week, and so I have gone for an alternating background colour for clarity.

I still have some more work to do on conditional formatting to accommodate the changing patterns for different months, but I thought I'd let you know of progress.

Hope this helps.

Pete

Thanks. Don't sweat it. We have been using the paper system for over 25 years.
The younger employees don't know what a paper and pen is unfortunately. LOL.
They are always looking for a computer.

Take care.

Rod

Okay, so I might be shooting myself in the foot here. Working with the spreadsheet from above I started making a VBA user form (maybe I shouldn't have) to make it easier for people to enter information without screwing up the data sheet.
So far from tips online it is going fairly well. One problem which I think I have seen on this site is with the cells that have formulas in them which are the last 3 columns, if I put those columns in the user form it overwrites the formulas.
I am using a date picker on the form to make it easier for the senior people to input dates. Is there any way to input the dates and the times in the user form without overwriting the formulas?
Or am I jumping the gun on this? Sorry if I am wasting peoples time on this. The idea of trying to create this application has me overwhelmed with it.

Thanks

Originally Posted by Pete_UK

Just to let you know that I'm still working on the calendar display off and on, so I haven't forgotten about it. In setting this up I've concluded that "Weekly" is not really a rental type, as the duration of the booking can be specified by the Start Date and End Date. (I appreciate that you might have a different charging rate for Weekly, but this isn't about working out the charges). The rental types are thus: Half Day AM, Half Day PM, Full Day, and Hourly. With an Hourly booking, I shall show the Start Time in front of the Name and Contact Number., whereas the other rental types will show just the Name and Contact No. As discussed earlier, if you have more than one hourly booking in an AM or PM slot, only the first (recorded) one can be displayed.

Each day of the calendar will be divided in two, for AM (9:00-14:00 bookings) and for PM (14:00-19:00 bookings), and Full Day bookings will span both. You can specify a Start Date and End Date (optional for one-day bookings) for each booking type, and the display will cover this duration automatically. The boats will be listed in the same relative position for each week, and so I have gone for an alternating background colour for clarity.

I still have some more work to do on conditional formatting to accommodate the changing patterns for different months, but I thought I'd let you know of progress.

Hope this helps.

Pete

Does this mean that for a weekly rental I would have to enter a persons name in 7 times?
Same as for 6 days, 6 times?

It is YOUR application, so you can do what you want to enhance it, but before you go too far I thought I'd let you know what I have settled on as regards the Bookings sheet.

I have a bunch of formulae in columns A to G (maybe even H as well) which convert the data from the booking sheet into a format that the calendar can make use of. This is all done automatically, and these columns can be hidden if required to prevent accidental changes being made to them.

I have the following headings in row 2 of the booking sheet itself:

K2: Conflict
L2: Name
M2: Contact Number
N2: Boat Rented
O2: Rental Type
P2: Start Date
Q2: End Date
R2: Start Time
S2: End Time

Data entry then starts at row 3. In addition, I have two further columns (T and U) which calculate the number of days' hire and give a cumulative sum of these days. There is a formula in K to identify any clashes, so the (manual) data entry is basically from columns L to S, although Start Time is only used for Hourly bookings (I may have a formula to indicate this is mandatory), and End Date is optional (only needed for bookings of more than 1 day). I don't use End Time, so that could also be considered optional, though it is useful for Hourly bookings. I have set up data validation drop-downs for the Boat Rented and for Rental Type, which are defined on a separate sheet called Ref_data.

If you are doing any further developments yourself and want to blend my calendar display seamlessly, it would help if you were to use my layout. I'm still carrying on with the conditional formatting top and bottom to get the display looking right.

Let me know if you need any other data to be recorded on the Bookings sheet.

Hope this helps.

Pete

You have opened Pandora,s Box.
There are has many views on the use of UserForms as there are Guru's and Expert's.
Just my 'pennyworth' if you use UserForms do all your calcs within your code (no on sheet formula).
You are then using the sheet as a pseudo database (just data storage).
You have already highlighted a problem without realising, fundamentally you cannot put data in a cell and retain formula.
torachan.

Originally Posted by radar1972

Does this mean that for a weekly rental I would have to enter a persons name in 7 times?
Same as for 6 days, 6 times?

No. You will have one entry and just specify the start and end dates. My formulae in columns A to G will automatically expand the data to suit each date range, as well as putting an entry in either the AM or the PM slot, or both. So if someone wants to hire a boat just for the afternoons but on 3 successive days then you will have the flexibility to record this in one record.

Hope this helps.

Pete

Thanks. I will leave you alone for now.

The weather is turning colder again here for a few days, so I might be able to devote more time to this project rather than being in the garden. I'll get back in touch when I'm happy with the display.

Pete

Well, this turned out to be a labour of love, but I think I've finally got all the wrinkles ironed out, so the file is attached with a calendar display of bookings.

I won't go through all the formulae, but essentially you enter data into the Bookings sheet in columns L to S. There are drop-downs in columns N and O to select the Boat and Rental Type respectively, and I have applied CF to columns R and S to show a warning blue background if the cells are empty and Hourly has been selected as the Rental Type. CF also turns the cells in columns N to S red if a clash is detected via the formula in column K. There are also formulae in columns T and U (in green) to evaluate the number of days rental for each booking and to give a cumulative count of these days. The booking table (and these 3 formulae) has been set up down to row 100, but you can easily extend this by copying the bottom row K100:U100 down further if required. If the end date is empty, it is assumed that the booking is for a single day.

To the left in columns A:H is a group of formulae which expand the data from the bookings table to make it suitable for the calendar display. The formula in column H also identifies potential clashes, but does so on a day-by-day basis, and some of these may not be clashes - e.g. if a boat is booked for an hour on a particular day, there is the possibility of a further hourly booking in that same AM or PM slot, but this is not necessarily a clash (only the first such booking will be displayed on the calendar, however). The data from the bookings table is expanded to take account of the date range for each booking, so that there is one entry per day for that booking. Consequently, there may be far more records in the blue area compared with the booking table, and so I have copied the formulae down to row 1000 - you can copy these down further if you think you may need more. Once you are happy with the set up, you can hide columns A to J in order to prevent accidental changes to the formulae, and to make the bookings table more visible.

It is possible to work out your charges for each booking by having further formulae on the right-hand side, but I've not tried to implement this.

As described earlier, the Calendar sheet gives a monthly view of the bookings, and the month and year can be changed in cells B2 and B1 respectively on that sheet. These are validated so only valid entries can be input. The calendar is arranged from Sunday to Saturday across the sheet, with 2 columns used for each day to correspond to AM or PM bookings. Up to 5 weeks can be displayed, but for this arrangement a 30-day month which starts on a Saturday, or a 31-day month which starts on either a Friday or a Saturday would need 6 weeks to display completely, as one or two days would spill into the 6th week. This tends to happen about 2 or 3 times in any one year. In order to avoid these "orphan" days (as I call them), I have arranged for these days to be shown in the first week of the calendar, and I have used a different coloured background for the date to make them stand out (see, for example, January, May and October in 2021).

The 10 boats are listed on the left and right sides of the calendar, and are colour-coded with alternate shades of blue. These colours are reproduced whenever there is a booking, so that the booking can easily be identified. Additionally, an hourly booking is shown with a purple background, for emphasis. These hourly bookings are shown with the time first, and all bookings show the name and contact number of the hirer.

Anyway, I hope this gives you what you were hoping for, and can act as a springboard for future development. You can see that I've added some sample data to test things out, but you can just delete this data (not the formulae !!) and then start playing with it yourself. I'd be grateful for any feedback from you when you have had chance to try it out, especially how it works through Drop Box as that is not something I've tried out myself. Good luck.

Hope this helps.

Pete

It works fairly well to what I was expecting. Thank you.
Couple things and you may have already answered this.
When there is a booking for a week from a PM slot to a Am slot(2:00pm - 2:00pm) a week later how can that be inputted without entering the persons name 3 times?
Right now I think it would have to be inputted PM slot first day, then full day for 6 days, and then last day an Am slot in order to show correctly in the calendar. Is this correct?
Technically it would be 7 days but it shows 8 days. I don't mind doing that but other people using this might find it annoying.
If it can only be entered once that would be great but could be opening up a can of worms.
I can save a second copy and fool around with it to see what I can figure out.

Thanks.

What you have suggested for a weekly booking starting PM and finishing AM is correct - see my comments in Post #13.

As I said before, this is your application, so you can now develop it further in whatever direction appeals to you.

Good luck.

Pete

Ok thanks, will continue to work with it.
Thanks again for all your hard work.

You're welcome.

If you think that has taken care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete