+ Reply to Thread
Results 1 to 9 of 9

Calculate rental spanning different seasonal rental rates

  1. #1
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Calculate rental spanning different seasonal rental rates

    Hi all,

    I am trying to calculate a total rental cost based on monthly seasonal rates, for example the pick up date may be 4 days before the end on the April rate and the drop off 20 days into the May rate.

    I believe I could do this in a long winded way using VBA but I am sure it could be achieved through a formula which at present defeats me.

    So the daily rate for April might be 10 and the daily rate for May 12 so in the example I used would give a total of 280.

    In the worksheet I would enter the pickup date and drop off date, the rates would be maintained on another sheet.

    Thanks in advance, I sure this is simple enough for someone more expert than I and I look forward to learning from the principle.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,095

    Re: Calculate rental spanning different seasonal rental rates

    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Calculate rental spanning different seasonal rental rates

    Thanks etaf

    The cels are commented with what I am trying to achieve.

    Really not good at working with dates so just need to know how to look up the rate table for the appropriate number of days.
    Attached Files Attached Files

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,095

    Re: Calculate rental spanning different seasonal rental rates

    you are showing 9 days in January and using the dayrate * 9
    BUT you are also showing a weekly rate, when does the weekly rate apply ?
    what would typically be the maximun rental period
    could it be Sep/Oct/Nov and so three rates are applicable or more months

  5. #5
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Calculate rental spanning different seasonal rental rates

    Typically I would apply the day rate up to 6 days, 7 days to 28 days would be the weekly rate and I would like to add a monthly rate over that period.

    With the rates varying from month to month due to demand I didn't want to complicate things to much at this point. If I could even get the calculation to apply the daily rate for the applicable days where the period straddles months of different rates then that would be a start. I don't understand how to have the formula lookup the rate table and extract that information for that calculation at present.

    If I can progress to the stage of using IF for different time spans and take into account different monthly rates then I would be a very happy bunny

  6. #6
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Calculate rental spanning different seasonal rental rates

    deleted duplicate message

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,095

    Re: Calculate rental spanning different seasonal rental rates

    i have setupthe calcs - in a progressive way - across columns I to O
    in O is the calc

    i have not included the return day - so thats easyto change and just add i

    this only works across 1 month
    and is a bit clunky

    have a look and see if its near what you need

    look at the days calc - and check - do you charge for day1 and the last day?

    i have also changed the table to have a date - so you can lookup

    it will need 2018,19 etc added , as it is year dependant

    I suspect a sumproduct() would be a better solution, but not sure how to setup
    OR even some Helper Columns may assist - to flag if a week, or a month- and then broken down a bit like i have done
    to calculate the combined value - in fact that maybe a better way to go , on reflection
    Attached Files Attached Files
    Last edited by etaf; 01-25-2017 at 02:15 PM.

  8. #8
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Thumbs up Re: Calculate rental spanning different seasonal rental rates

    Wayne, thank you so much for your suggestions, your formulas give me something to build on and that is what I wanted rather than someone to do the job for me.

    Thanks again

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,095

    Re: Calculate rental spanning different seasonal rental rates

    you are welcome, thanks for the rep

    you could use a helper column to find the number of weeks - in case this helps at all

    INT( (c3-b3)/7 ) - gets the complete weeks
    MOD ( (c3-b3), 7) - gives you the remainder days

+ 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. Car rental and its accounts help needed
    By asifmughal150 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-17-2015, 09:38 AM
  2. Replies: 1
    Last Post: 11-12-2013, 03:22 PM
  3. 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
  4. Rental Spreadsheet
    By Emmak153 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-04-2012, 07:35 PM
  5. Replies: 5
    Last Post: 10-13-2012, 06:26 AM
  6. [SOLVED] Car Rental
    By limecity in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-18-2009, 08:20 PM
  7. Rental instalments formula
    By Phops in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-09-2007, 10:29 PM

Tags for this Thread

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