Hi There,

Select 6*7 range and enter array-formula (Honours to Jwalk!)
Put in B6 a day in month of may 2005.

You'll see 1st may is missing! Possible to get it in somehow?


=IF(MONTH(DATE(YEAR(B6);MONTH(B6);1))-
MONTH(DATE(YEAR(B6);MONTH(B6);1)-
(WEEKDAY(DATE(YEAR(B6);MONTH(B6);1))-2)+
{0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1);"";DATE(YEAR(B6);
MONTH(B6);1)-(WEEKDAY(DATE(YEAR(B6);
MONTH(B6);1))-2)+{0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1)

PS. US-XLversions will need to replace ; by , and / by ;

Cheers Sige

Sige,

Both instances of this array:

{1\2\3\4\5\6\7}

need to be

{0\1\2\3\4\5\6}


HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Hi There,

Select 6*7 range and enter array-formula (Honours to Jwalk!)
Put in B6 a day in month of may 2005.

You'll see 1st may is missing! Possible to get it in somehow?


=IF(MONTH(DATE(YEAR(B6);MONTH(B6);1))-
MONTH(DATE(YEAR(B6);MONTH(B6);1)-
(WEEKDAY(DATE(YEAR(B6);MONTH(B6);1))-2)+
{0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1);"";DATE(YEAR(B6);
MONTH(B6);1)-(WEEKDAY(DATE(YEAR(B6);
MONTH(B6);1))-2)+{0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1)

PS. US-XLversions will need to replace ; by , and / by ;

Cheers Sige

Hi Bernie,

May has also 1st! yipie...

But how to Mondays as my first column? While maintaining 1st may!

Cheers Sige

Hi Sige

The formula I posted for you on 20/09 does work for every month except May.
I had not tested that.
Bernie, changing the sequence from 1/2/3/4/5/6/7 to 0/1/2/3/4/5/6 has
achieved it working for May, but has put the starting day back to Sunday.

I had ameneded the 2 parts of the formula
(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1)

to (WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-2) in order to get it to start Mondays.

Changing the new formula from
(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-2)
to
(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-3)
puts it back to starting Monday, but the problem of 1st May 2005 returns.

I can't see how to solve this at the moment. It appears that as long as you
start the week on Sundays, it will always work (with either vesion of the
formula), but it is trying to make the weks start on Monday gives the problem.


Regards

Roger Govier


wrote:
Hi Bernie,

May has also 1st! yipie...

But how to Mondays as my first column? While maintaining 1st may!

Cheers Sige

Sige,

The simplest thing is to add an eighth column of simple links (DO NOT change
the formula), and use a reference to the first column, offset by one row.
Then hide the first column.

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Hi Bernie,

May has also 1st! yipie...

But how to Mondays as my first column? While maintaining 1st may!

Cheers Sige

Hi Bernie thanks,

Making it a 7*7 matrix works as well ...

=IF(MONTH(DATE(YEAR(B6);MONTH(B6);1))-
MONTH(DATE(YEAR(B6);MONTH(B6);1)-
(WEEKDAY(DATE(YEAR(B6);MONTH(B6);1))-2)+
{-1;0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1);"";DATE(YEAR(B6);
MONTH(B6);1)-(WEEKDAY(DATE(YEAR(B6);
MONTH(B6);1))-2)+{-1;0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1)

Brgds Sige

Shew, saved 1 jan 2006!
Imagine ...NO NEW YEAR ...this year.