Help with date/cost formula

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

This is a booking form for a holiday rental property which is completed by
the `renter` and the idea is my form works out the total cost and dates when
deposit and balance are due, I thought this might help to explain what I am
trying to achieve.


In K7,J7,I7 & G7,F7,E7
I have dates eg 22 07 2010 & 29 07 2010

So in cell J24 I have =DATE(K7,J7,I7)-DATE(G7,F7,E7)
which gives me the number of days which is 7
In K24 I have a cost (£42) which is J24* N3 (which is 7 X 24) which gives
me a total cost for the 7 days. (£294)

This is for someone renting my property 7 days at £42 per day and the amount
is worked out by the renter putting in the dates of their holidays.

Now this system works fine except for one area which is beyond my
capabilities, although I can alter easily enough manually.

I would like to be able to have this work out a cost based on different
prices for different months. for example, if they rent in May June and
September the cost is £39 per day and if they rent in July or August the
cost is £42 per day. So they could rent for example 2 days in June at £39
and 5 days in July at £42 if the days booked are at the last couple of days
of June.

Is there an easy formula for this that I could manage as I need to change
the charges from time to time.

many thanks
 
Hi

On another sheet
In cells D2:P2 enter the ending date for each month of the year
31/01/2010 to 31/12/2010
In D1:P1 enter the daily charge for that month
In B3 =DATE(Sheet1!G7,Sheet1!F7,Sheet1!E7)
in C3 DATE(Sheet1!K7,Sheet1!J7,Sheet1!I7)

in D3
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B3&":"&$C3)),
ROW(INDIRECT(DATE(YEAR(D$2),MONTH(D$2),0)+1&":"&D$2)),0)))
Copy across through E3:P3

Cost for stay
=SUMPRODUCT(D1:P1,D3:P3)
 
Thanks for the reply,

I think I have entered all correctly although I did make the following
change..."Copy across through E3:P3" I made this E3:O3, for 12 months. (I
also adjusted any ref to P to O in the formulas)
Having said that the sheets fails to give an amount, but, there is no error
message anywhere so it does look as if the formulas are indeed correct.
Should there be a reference from sheet1 (the booking form) to the `new
sheet` possibly in here somewhere.....

Cost for stay
=SUMPRODUCT(D1:P1,D3:P3)

thanks
 
Hi

It works fine for me.

Do you want to send me a copy of your workbook, and i will take a look.
To mail direct
roger at technology4u dot co dot uk
Change the at and dots, and remove spaces to mail a valid email address
 
Back
Top