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.

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

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

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