Help to create a date formula
 

I have 2 sets of cells.
Set 1. C7,D7, E7 this is for start DD/MM/YYYY
Set 2. G7, H7,I7 this is for to end DD/MM/YYYY

The above represents weekly rental for property eg start 26/06/2006 end
03/07/2006 so one week rental.

In cell M7 I need a formula for the above to show £200 for that week
However if they rent for 2 weeks eg 26/06/06 to 10/07/2006 then also M8 to
show £200.
That would give me 2 entries of £200 one for each week, this is then
totalled further down the page.

The year YYYY I dont think is relevent and I somehow need to work out if one
week or two from the dates
and have the formula enter a £200 or 2 x £200 in the apprpriate cells.

Any help appreciated.

Help to create a date formula
 

Hi!

Assuming that the date span is always full week(s):

Enter this formula in M7 and copy down as needed:

=IF(COUNT(C$7:E$7,G$7:I$7)<6,"",IF(ROWS($1:1)<=(DA TE(I$7,H$7,G$7)-DATE(E$7,D$7,C$7))/7,200,""))

Biff

"S S" wrote in message
...
I have 2 sets of cells.
Set 1. C7,D7, E7 this is for start DD/MM/YYYY
Set 2. G7, H7,I7 this is for to end DD/MM/YYYY

The above represents weekly rental for property eg start 26/06/2006 end
03/07/2006 so one week rental.

In cell M7 I need a formula for the above to show £200 for that week
However if they rent for 2 weeks eg 26/06/06 to 10/07/2006 then also M8
to show £200.
That would give me 2 entries of £200 one for each week, this is then
totalled further down the page.

The year YYYY I dont think is relevent and I somehow need to work out if
one week or two from the dates
and have the formula enter a £200 or 2 x £200 in the apprpriate cells.

Any help appreciated.

Help to create a date formula
 

Try this

=IF($G7-(COLUMN(A1)-1)*7$C7,200,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"S S" wrote in message
...
I have 2 sets of cells.
Set 1. C7,D7, E7 this is for start DD/MM/YYYY
Set 2. G7, H7,I7 this is for to end DD/MM/YYYY

The above represents weekly rental for property eg start 26/06/2006 end
03/07/2006 so one week rental.

In cell M7 I need a formula for the above to show £200 for that week
However if they rent for 2 weeks eg 26/06/06 to 10/07/2006 then also M8
to
show £200.
That would give me 2 entries of £200 one for each week, this is then
totalled further down the page.

The year YYYY I dont think is relevent and I somehow need to work out if
one
week or two from the dates
and have the formula enter a £200 or 2 x £200 in the apprpriate cells.

Any help appreciated.

Help to create a date formula
 

I think each date is spread across 3 cells. At least, that's how I read it.

D | M | Y

Biff

"Bob Phillips" wrote in message
...
Try this

=IF($G7-(COLUMN(A1)-1)*7$C7,200,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"S S" wrote in message
...
I have 2 sets of cells.
Set 1. C7,D7, E7 this is for start DD/MM/YYYY
Set 2. G7, H7,I7 this is for to end DD/MM/YYYY

The above represents weekly rental for property eg start 26/06/2006 end
03/07/2006 so one week rental.

In cell M7 I need a formula for the above to show £200 for that week
However if they rent for 2 weeks eg 26/06/06 to 10/07/2006 then also M8
to
show £200.
That would give me 2 entries of £200 one for each week, this is then
totalled further down the page.

The year YYYY I dont think is relevent and I somehow need to work out if
one
week or two from the dates
and have the formula enter a £200 or 2 x £200 in the apprpriate cells.

Any help appreciated.

Help to create a date formula
 

Yip that worked a treat....thanks

"Biff" wrote in message
...
Hi!

Assuming that the date span is always full week(s):

Enter this formula in M7 and copy down as needed:

=IF(COUNT(C$7:E$7,G$7:I$7)<6,"",IF(ROWS($1:1)<=(DA TE(I$7,H$7,G$7)-DATE(E$7,D$7,C$7))/7,200,""))

Biff

"S S" wrote in message
...
I have 2 sets of cells.
Set 1. C7,D7, E7 this is for start DD/MM/YYYY
Set 2. G7, H7,I7 this is for to end DD/MM/YYYY

The above represents weekly rental for property eg start 26/06/2006 end
03/07/2006 so one week rental.

In cell M7 I need a formula for the above to show £200 for that week
However if they rent for 2 weeks eg 26/06/06 to 10/07/2006 then also M8
to show £200.
That would give me 2 entries of £200 one for each week, this is then
totalled further down the page.

The year YYYY I dont think is relevent and I somehow need to work out if
one week or two from the dates
and have the formula enter a £200 or 2 x £200 in the apprpriate cells.

Any help appreciated.

Help to create a date formula
 

You're welcome. Thanks for the feedback!

Biff

"S S" wrote in message
k...
Yip that worked a treat....thanks

"Biff" wrote in message
...
Hi!

Assuming that the date span is always full week(s):

Enter this formula in M7 and copy down as needed:

=IF(COUNT(C$7:E$7,G$7:I$7)<6,"",IF(ROWS($1:1)<=(DA TE(I$7,H$7,G$7)-DATE(E$7,D$7,C$7))/7,200,""))

Biff

"S S" wrote in message
...
I have 2 sets of cells.
Set 1. C7,D7, E7 this is for start DD/MM/YYYY
Set 2. G7, H7,I7 this is for to end DD/MM/YYYY

The above represents weekly rental for property eg start 26/06/2006 end
03/07/2006 so one week rental.

In cell M7 I need a formula for the above to show £200 for that week
However if they rent for 2 weeks eg 26/06/06 to 10/07/2006 then also M8
to show £200.
That would give me 2 entries of £200 one for each week, this is then
totalled further down the page.

The year YYYY I dont think is relevent and I somehow need to work out if
one week or two from the dates
and have the formula enter a £200 or 2 x £200 in the apprpriate cells.

Any help appreciated.

Help to create a date formula
 

Yes the date is across 3 cells.


"Biff" wrote in message
...
I think each date is spread across 3 cells. At least, that's how I read it.

D | M | Y

Biff

"Bob Phillips" wrote in message
...
Try this

=IF($G7-(COLUMN(A1)-1)*7$C7,200,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"S S" wrote in message
...
I have 2 sets of cells.
Set 1. C7,D7, E7 this is for start DD/MM/YYYY
Set 2. G7, H7,I7 this is for to end DD/MM/YYYY

The above represents weekly rental for property eg start 26/06/2006 end
03/07/2006 so one week rental.

In cell M7 I need a formula for the above to show £200 for that week
However if they rent for 2 weeks eg 26/06/06 to 10/07/2006 then also M8
to
show £200.
That would give me 2 entries of £200 one for each week, this is then
totalled further down the page.

The year YYYY I dont think is relevent and I somehow need to work out if
one
week or two from the dates
and have the formula enter a £200 or 2 x £200 in the apprpriate cells.

Any help appreciated.