Array Formula Calendar

[SIGE_GOEVAERTS]

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

 

[Bernie Deitrick]

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

 

[SIGE_GOEVAERTS]

Hi Bernie,

May has also 1st! yipie...

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

Cheers Sige

 

[Roger Govier]

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

 

[Bernie Deitrick]

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

 

[SIGE_GOEVAERTS]

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

 

[SIGE_GOEVAERTS]

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