Fill in Date problem

L

Lisa

I am trying to fill in weekly dates from entering the first Monday in April,
which I can do by A1+7....but I have the months arranged in blocks of 5 and
want the cell to remain blank if there is only 4 weeks in that month and put
the date in the box below it.

Any ideas please?

Regards,
Lisa
 
B

Bryan Hessey

in the 5th week (A5)

=IF(MONTH(A4+7)=MONTH(A4),A4+7,"")

in the next first week

=IF(A5="",A4+7,A5+7)

Hope this helps

--
 
B

Bryan Hessey

A further thought, if your dates run down the A column in sequence,
use,
in A2


=IF(MOD(ROW(),5)=0,IF(MONTH(A1+7)=MONTH(A1),A1+7,""),IF(MOD(ROW(),5)=1,IF(A1="",H0+7,A1+7),A1+7))

and in A3


=IF(MOD(ROW(),5)=0,IF(MONTH(A2+7)=MONTH(A2),A2+7,""),IF(MOD(ROW(),5)=1,IF(A2="",A1+7,A2+7),A2+7))


and formula copy that as far as you want dates.

(yes, I know that H0 is not valid)

Hope this helps

--
 
L

Lisa

"Bryan Hessey" <[email protected]>
wrote in message
in the 5th week (A5)

=IF(MONTH(A4+7)=MONTH(A4),A4+7,"")

in the next first week

=IF(A5="",A4+7,A5+7)

Hope this helps
Click to expand...

That's fabulous, Thanks!!

I was nearly there - just hadn't worked out the MONTH bit.

Lisa
 
B

Bryan Hessey

Good to see, and thanks for the response, but check that you picked u
the amendment to the original post.
The formula for A3 should refer back mainly to A2, with one referenc
to A1 (replacing the reference to H0 in formula for A2)

--
 
Top