Calendar Formula

[Steved]

Hello From Steved
Below I was posted the following Formula
In Cell A1 I put 2004
In Cell A2 I put 6
In The Formula I Replace aYear with A1 and aMonth, A2
I can only get it to work on the first day of the month
The below formula is what I amm looking for as the first
column will be blank until it finds the second day in this
case Tuesday and will the put in "1" but this ios all I
get, please explain to me what I am doing wrong,

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:

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),"")

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]

Steve,

1.It's an ARRAY formula.
2.It's returning an ARRAY (meaning many cells of info).

Copy the formula from the post.
To comply with 2, you must SELECT a 7 lines by 6 columns range.
Press F2 (to edit), paste the formula and make sure there are no returns
To comply with 1, press simultanously Ctrl-Shift-Enter (to enter it as an ARRAY
formula).

Regards,

Daniel M.

 

[Steved]

Thankyou Daniel

I will admit I was doing Control Shift and Enter, my
mistake was that I failed to Press F2.

Daniel may I make just a small change to your formula
so that it puts the day across the top
Sun, Mon, Tue, and so on to Sat
6 Lines and 7 Column Range
Also I have formated A1 as MMM-YYYY could it please refer
to that Cell as I use a new calendar the following Month.

Thankyou for your kind patience.

 

[Daniel.M]

Hello,

Daniel may I make just a small change to your formula
so that it puts the day across the top
Sun, Mon, Tue, and so on to Sat
6 Lines and 7 Column Range

Have a Mtx name defining your Matrix and you'll be able to have it the way you
want.

So, press Ctrl-F3, ' or Menu/Insert/Name/Define
Name: Mtx
Refers To: ={1,2,3,4,5,6,7}+{0;1;2;3;4;5}*7

Then, select a 6 by 7 Range, F2 and paste the following formula
=IF(MONTH(DATE(A1,A2,1)-WEEKDAY(DATE(A1,A2,1))+Mtx)=A2,
DATE(A1,A2,1)-WEEKDAY(DATE(A1,A2,1))+Mtx,"")

This gives you dates from Sunday to Saturday as per your new request.

Also I have formated A1 as MMM-YYYY could it please refer
to that Cell as I use a new calendar the following Month.

I don't understand. A1 should be a year number and A2 should be a month number
If you want today's values, A1=YEAR(TODAY()) and A2=MONTH(TODAY())
If you want another day's value, substitute Today() with the cell containing the
date in the A1 and A2 formula. This seems flexible.

However, if you meant having any date in B1, the following ARRAY formula should
produce a 6 by 7 range cells of dates in the same month :
=IF(MONTH(B1-DAY(B1)+1-WEEKDAY(B1-DAY(B1)+1)+Mtx)=MONTH(B1),
B1-DAY(B1)+1-WEEKDAY(B1-DAY(B1)+1)+Mtx,"")

Regards,

Daniel M.

 

[Steved]

Thankyou Daniel.

-----Original Message-----
Hello,


Have a Mtx name defining your Matrix and you'll be able to have it the way you
want.

So, press Ctrl-F3, ' or Menu/Insert/Name/Define
Name: Mtx
Refers To: ={1,2,3,4,5,6,7}+{0;1;2;3;4;5}*7

Then, select a 6 by 7 Range, F2 and paste the following formula
=IF(MONTH(DATE(A1,A2,1)-WEEKDAY(DATE(A1,A2,1))+Mtx)=A2,
DATE(A1,A2,1)-WEEKDAY(DATE(A1,A2,1))+Mtx,"")

This gives you dates from Sunday to Saturday as per your new request.
Month.

I don't understand. A1 should be a year number and A2 should be a month number
If you want today's values, A1=YEAR(TODAY()) and A2=MONTH (TODAY())
If you want another day's value, substitute Today() with the cell containing the
date in the A1 and A2 formula. This seems flexible.

However, if you meant having any date in B1, the following ARRAY formula should
produce a 6 by 7 range cells of dates in the same month :
=IF(MONTH(B1-DAY(B1)+1-WEEKDAY(B1-DAY(B1)+1)+Mtx)=MONTH (B1),
B1-DAY(B1)+1-WEEKDAY(B1-DAY(B1)+1)+Mtx,"")

Regards,

Daniel M.


.

 

[Daniel.M]

Thank you Daniel.

Also, if you only need the numbers not the dates :
=IF(MOD(Mtx-WEEKDAY(B1-DAY(B1)+1),45)<32-DAY(B1-DAY(B1)+32),
1+Mtx-WEEKDAY(B1-DAY(B1)+1),"")

Regards,

Daniel M.