+ Reply to Thread
Results 1 to 17 of 17

Looking to create a boat rental booking sheet(s)

  1. #1
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,681

    Re: Looking to create a boat rental booking sheet(s)

    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

  3. #3
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

    Quote Originally Posted by BadlySpelledBuoy View Post
    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.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: Looking to create a boat rental booking sheet(s)

    Not too sure about helping with a calendar, as there are contributors whom know more about them than I do.
    Perhaps the following will be of some help:
    Renter's Name is manually entered into column A
    Boat and Rental Type may be selected from drop downs in columns B:C
    Start Date is manually entered in column D
    End Date, Start Time, End Time and Conflict are populated using formulas in columns E:H
    The formula for column E is: =IF(COUNTIFS(X$2:X$4,C2),D2,IF(C2=X$5,D2+7,""))
    The formula for column F is: =IF($A2="","",INDEX(Y$2:Y$6,MATCH($C2,$X$2:$X$6,0)))
    The formula for column G is: =IF($A2="","",INDEX(Z$2:Z$6,MATCH($C2,$X$2:$X$6,0)))
    The formula for column H is: =IF(SUMPRODUCT((B$2:B2=B3)*(D$2:D2=D3)*(F$2:F2<F3)*(G$2:G2>=G3)),TRUE,"")
    If there is a conflict in scheduling then the record that produced the conflict will highlight red using: =$H2=TRUE
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

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

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: Looking to create a boat rental booking sheet(s)

    I have never used date picker, however in reading the system requirements I found the following: "Requires the desktop edition of Microsoft Excel"
    As post #3 states that the spreadsheet will be shared on an iPad, I can't answer the question. My suggestion would be to experiment with a cell in column D.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

    Thank you. Will give it a shot.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Looking to create a boat rental booking sheet(s)

    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

  9. #9
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

    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.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Looking to create a boat rental booking sheet(s)

    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

  11. #11
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

    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.

  12. #12
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

    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?

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Looking to create a boat rental booking sheet(s)

    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

  14. #14
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

    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

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Looking to create a boat rental booking sheet(s)

    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

  16. #16
    Registered User
    Join Date
    02-18-2021
    Location
    Bancroft, Ontario Canada
    MS-Off Ver
    2019
    Posts
    9

    Re: Looking to create a boat rental booking sheet(s)

    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

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,501

    Re: Looking to create a boat rental booking sheet(s)

    Heard a Stephen Wolfram joke told about looking at a picture his daughter drew of a laptop computer.
    SW: When I was growing up we didn't draw pictures of computers.
    Daughter: They hadn't invented paper?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create booking system in excel!
    By aretha in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 10-10-2018, 08:16 PM
  2. Assets booking sheet - tracking most recent booking
    By TommyTommyTommy in forum Excel General
    Replies: 12
    Last Post: 08-08-2018, 10:53 PM
  3. Calculate rental spanning different seasonal rental rates
    By Orada in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-25-2017, 04:00 PM
  4. how to create a booking calendar???
    By ajs100 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-11-2014, 08:34 AM
  5. Trying to Create a booking in and out system
    By ellisp999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2013, 06:26 PM
  6. Boat Rental Booking System
    By agadalla in forum Excel General
    Replies: 2
    Last Post: 08-05-2013, 04:31 PM
  7. Calculate Rental Cost With Varying Rates Based On Rental Days
    By jmenh in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2013, 03:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1