calendar to 12 month calendar

[Steved]

Hello from Steved
I was given this formula below for a monthly calendar now
I would like to make a year calendar using A1 for the
Year, how do I go about this please.

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

=IF(MONTH(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1)+1)+Mtx)=MONTH
(A1),A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1)+1)+Mtx,"")

Thankyou.

 

[Norman Harker]

Hi Steved!

Here's a formula approach posted by Bernie Deitrick last year.

A1:
Year number (eg) 2004

A2:A4
=IF(MONTH(DATE($A$1,INT((ROW()-1)/4)+1,COLUMN()))=MOD(INT((ROW()-1)/4),12)+1,DATE($A$1,INT((ROW()-1)/4)+1,COLUMN()),"")

A2 Format mmmm yyyy
A3 Format d
A4 Format ddd

Copy across A3:A4 to AE3:AE4

A2:AE4
Copied wherever for February and so on.

 

[Steved]

Thankyou Norman

-----Original Message-----
Hi Steved!

Here's a formula approach posted by Bernie Deitrick last year.

A1:
Year number (eg) 2004

A2:A4
=IF(MONTH(DATE($A$1,INT((ROW()-1)/4)+1,COLUMN()))=MOD(INT ((ROW()-1)/4),12)+1,DATE($A$1,INT((ROW()-1)/4)+1,COLUMN
()),"")

A2 Format mmmm yyyy
A3 Format d
A4 Format ddd

Copy across A3:A4 to AE3:AE4

A2:AE4
Copied wherever for February and so on.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)



.

 

[Bob Phillips]

Steve,

You might want to take a look at the calendar shown at

http://www.xldynamic.com/source/xld.CF.html#calendar
Using Conditional Formatting - Calendar

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steved]

Hello from Steved

I would like it to be 7 col's, by 6 rows for each month
please.
Thankyou

 

[Daniel.M]

Steve,

I also gave you that one:
IF(MONTH(DATE($A$1,A2,1)-WEEKDAY(DATE($A$1,A2,1))+Mtx)=A2,
DATE($A$1,A2,1)-WEEKDAY(DATE($A$1,A2,1))+Mtx,"")

So for July :
Put the year (2004) in A1
Select the 7 col by 6 rows and Array-enter the formula.

IF(MONTH(DATE($A$1,7,1)-WEEKDAY(DATE($A$1,7,1))+Mtx)=7,
DATE($A$1,7,1)-WEEKDAY(DATE($A$1,7,1))+Mtx,"")

Obviously, you can select a cell to contain the number 7 (that's the A2 in the
initial formula) instead of hardcoding it into the formula. I don't know where
you want your month dates (7 cols by 6 rows) to appear. That's for you to decide
as long as you understand the principle: you'll have to do that for the 12
months of the year being sure it refers to a cell containing the proper month
number, or using INT(ROW()/8).

Got it? If not, give where (the precise cells for January, February, etc) on the
sheet you want the info to appear and it'll be easier to customize the formula.

Regards,

Daniel M.

 

[Steved]

Hello Daniel from Steved

Daniel I was at work When I asked for your formula. I am at home and decided
to create your calendar
12 times to create a full year, hence I forgot to go back and look more
fully at your formulas as at the time
I only needed a individual month.

Daniel I have laid out 12 individual months of your calendar which is for a
complete year.

The calendar looks in A1 to change the month hence all other 11 calendars
are the same

What I want to do with your formula in A2 format ( "yyyy" ) is to have it
update the other 11 calendars

Below is your format I used to create the 12 individual Calendars the layout
is Sun , Mon, to Sat row wise.

=IF(MONTH(A2-DAY(A2)+1-WEEKDAY(A2-DAY(A2)+1)+Mtx)=MONTH(A2),A2-DAY(A2)+1-WEEKDAY(A2-DAY(A2)+1)+Mtx,"")

 

[Steved]

Thanks Bob I am wanting 12 individuals months, thankyou for pointing me to
this site.

 

[Daniel.M]

What???!!!

I (re)gave you on my previous message a formula to construct a 6 by 7 grid for
ONE MONTH when you know the year (in A1) and the month number (in A2, 1-12).

=IF(MONTH(DATE($A$1,A2,1)-WEEKDAY(DATE($A$1,A2,1))+Mtx)=A2,
DATE($A$1,A2,1)-WEEKDAY(DATE($A$1,A2,1))+Mtx,"")

It assumes the year is in A1 and the month number is in A2. Have you used it? Do
you understand that depending on the number (1 to 12) in A2, the grid will show
the proper month.

Now, you want ALL the months of a given year displayed.
So you'll have to put 12 grids of the formula but changing the A2 part to have
the numbers 1 to 12 instead. Then, you'll get all the months of year A1 in 12
grids (6 by 7 each).

Regards,

Daniel M.

 

[Steved]

Hello Daniel
Sorry I was using
=IF(MONTH(A2-DAY(A2)+1-WEEKDAY(A2-DAY(A2)+1)+Mtx)=MONTH(A2),A2-DAY(A2)+1-WEEKDAY(A2-DAY(A2)+1)+Mtx,"")

I am now using =IF(MONTH(DATE($A$1,A2,1)-WEEKDAY(DATE($A$1,A2,1))+Mtx)=A2,
DATE($A$1,A2,1)-WEEKDAY(DATE($A$1,A2,1))+Mtx,"")
Ok A1 I have formated as ("yyyy"), A2 as ("m") and entered as array formula
contrl shift enter,
I am getting #NUM!
The days are row wise ie Sun to Sat is this my problem

 

[Daniel.M]

Last try:

Ok A1 I have formated as ("yyyy"), A2 as ("m") and entered as array formula
contrl shift enter,
I am getting #NUM!

You put a YEAR number in A1 (NOT a date formatted as yyyy).
So put 2004 in A1

Your put a month number in A2 (NOT a date formatted as m)
So put 7 (or any number in 1,2,...,12) in A2

Results should be a 7 by 6 grid of the July 2004 dates.

Regards,

Daniel M.

 

[Steved]

Thanks Daniel Thats what I have now done.

Thanks for all your help.

Cheers.