Help with formula. dates/money

[S]

I have 3 cells which I enter a start date this produces £229 in cell I23
=INT((DATE(I3,H3,G3)-DATE(E3,D3,C3))/7)*229
I then have 3 cells to enter a finish date this also produces £299 in cell
I24 =INT((DATE(I3,H3,G3)-DATE(E3,D3,C3))/7)*229

The problem I have is that if the start date and finish date equals 2 weeks
then I get 2 entries for 229 which is ok
But if the start and finish date is only 1 week I still get 2 entries of
£229

I understand why it is doing this but how do I alter the formula so that if
the difference between the start and the finish date is only 1 week then I
only get one entry of £229 in cellI23 and cell I24 is left blank.

thanks

 

[Sandy Mann]

Hi S,

I don't understand what it is that you are asking. The formula I gave you
was for the whole elapsed time:

The Start date is:
DATE(I3,H3,G3)

The finish date is:
DATE(E3,D3,C3)

The elapsed number of days is:
DATE(I3,H3,G3)-DATE(E3,D3,C3))

Divide the days by 7 to give weeks:
DATE(I3,H3,G3)-DATE(E3,D3,C3))/7

This however will give a number like 1.142857143

Cut this short to just a whole number of weeks:
INT(DATE(I3,H3,G3)-DATE(E3,D3,C3))/7)

Multiply this by the weekly rate:

INT(DATE(I3,H3,G3)-DATE(E3,D3,C3))/7)*229

The other formula I gave you with MOD() in it works out the part weeks

Can you explain what it is you are trying to do, I mean what happens if the
elapsed time is more then two weeks?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Falkirk may not be a ancient but at least you've got the wheel <g>

 

[S]

Hi Sandy
After using you original formulas (which do work) I realised I had another
problem in getting this to work and thought best to uncomplicate it by
trying to work out part weeks, I can adjust that ok if need be.

To try and explain what I am doing....
I have a booking form and 99% of the persons booking book either 1 week or 2
weeks
say if someone books 1-14 May I have 2 cells each showing £229 which is ok
if they book 2 weeks (1 for each week)
If someone books only 1 week say 1-8 May I still have the 2 cells showing
£229
I somehow (if possible) need the formula to realise it is only 1 week and
just enter £229 for1 week only and leave the other cell as zero.

I hope this explains it.

 

[Sandy Mann]

Hi Again S,

If all you want is £299 for week 1 and £299 for week 2 then:

I23: =IF(DATE(I3,H3,G3)-DATE(E3,D3,C3)>=7,229,0)

I24: =IF(DATE(I3,H3,G3)-DATE(E3,D3,C3)>=14,229,"")

But what happens if there are more then two weeks?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[S]

That worked perfectly.
I have only ever had 1 booking in 3 years that booked for 3 weeks, it is
mostly 1 or 2 weeks, so I would just do a manual addition.
Thats what I did last time.
I (for me) think it is best to keep it simple, yes it would be great to
cover all options but what started out as a simple automated booking form I
have realised can get complicated very quickly.
as in what if they book for 3 weeks
what if they book for 2 full weeks and then part of a 3rd week
what if they want a discount ( I offer a slight discount if they book now
for next year.)
It would be easier for me just to rent by the day but most people excpect to
see a weekly rental charge, however I might take a look at this option on
the booking form as it might be more manageable.(that is less complicated.)

S