Formula for mahing this calendar

[Steved]

Hello from Steved
I copied the below Calendar, May I have a formula to
build it please.

Monday 7 14 21 28
Tuesday 1 8 15 22 29
Wednesday 2 9 16 23 30
Thursday 3 10 17 24
Friday 4 11 18 25
Saturday 5 12 19 26
Sunday 6 13 20 27

Thankyou.

 

[Daniel.M]

Hi Steve,

Assuming:
aYear : a cell or Name containing the year ( 2004)
aMonth: a cell or Name containing the month (6)

Select a 7 lines by 6 columns area and enter the following Array formula:

=IF(MONTH(DATE(aYear,aMonth,1+{1;2;3;4;5;6;7})-WEEKDAY(DATE(aYear,aMonth,0))+{0,
1,2,3,4,5}*7)=aMonth,DATE(aYear,aMonth,1+{1;2;3;4;5;6;7})-WEEKDAY(DATE(aYear,aMo
nth,0))+{0,1,2,3,4,5}*7,"")

Format the resulting cells as "d" ' without the quotes
BTW, those are real dates, if you really only want the numbers, use
the following formula (and format as Number or General)

=IF(MONTH(DATE(aYear,aMonth,1+{1;2;3;4;5;6;7})-WEEKDAY(DATE(aYear,aMonth,0))+{0,
1,2,3,4,5}*7)=aMonth,DAY(DATE(aYear,aMonth,1+{1;2;3;4;5;6;7})-WEEKDAY(DATE(aYear
,aMonth,0))+{0,1,2,3,4,5}*7),"")

The previous formulas could be simplified by using names for constant arrays but
I'll leave that to you. Also you can adapt for Sunday as the starting day in a
week by having "aMonth,1" instead of "aMonth,0"

For a concrete implementation (in French),
http://perso.wanadoo.fr/frederic.sigonneau/code/Calendriers/MiniCalAnnuel6.zip

Regards,

Daniel M.

 

[Steved]

Thankyou Daniel

-----Original Message-----
Hi Steve,

Assuming:
aYear : a cell or Name containing the year ( 2004)
aMonth: a cell or Name containing the month (6)

Select a 7 lines by 6 columns area and enter the following Array formula:

=IF(MONTH(DATE(aYear,aMonth,1+{1;2;3;4;5;6;7})-WEEKDAY (DATE(aYear,aMonth,0))+{0,
WEEKDAY(DATE(aYear,aMo
nth,0))+{0,1,2,3,4,5}*7,"")

Format the resulting cells as "d" ' without the quotes
BTW, those are real dates, if you really only want the numbers, use
the following formula (and format as Number or General)

=IF(MONTH(DATE(aYear,aMonth,1+{1;2;3;4;5;6;7})-WEEKDAY (DATE(aYear,aMonth,0))+{0,
{1;2;3;4;5;6;7})-WEEKDAY(DATE(aYear
,aMonth,0))+{0,1,2,3,4,5}*7),"")

The previous formulas could be simplified by using names for constant arrays but
I'll leave that to you. Also you can adapt for Sunday as the starting day in a
week by having "aMonth,1" instead of "aMonth,0"

For a concrete implementation (in French),
http://perso.wanadoo.fr/frederic.sigonneau/code/Calendrier s/MiniCalAnnuel6.zip

Regards,

Daniel M.




.