Fill in Date problem
 

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

Fill in Date problem
 

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

--

Lisa Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535305

Fill in Date problem
 

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

--

Bryan Hessey Wrote:
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

--


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535305

Fill in Date problem
 

"Bryan Hessey"
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

That's fabulous, Thanks!!

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

Lisa

Fill in Date problem
 

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

--

Lisa Wrote:
"Bryan Hessey"

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

That's fabulous, Thanks!!

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

Lisa


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535305