How to create monthly calendar on spreadsheet

[may1hem]

I'm using Excel 2007 and need to create a monthly calendar in separate worksheets.

So sheet 1 should show Jan 2010, sheet 2 should show Feb 2010, etc.

[tt]------------------------
January 2010
Mon 4 11 18 25
Tue 5 12 19 26
Wed 6 13 20 27
Thu 7 14 21 28
Fri 1 8 15 22 29
------------------------[/tt]

I have created one where I write "01/01/2010" in a separate cell above the calendar to set the month, but then I need to manually set the 1st of the month in the calendar because I don't know how to get it to automatically work out which day on the calendar is the 1st.

Any ideas how to make this work automatically?

 

[Andrzejek]

which day on the calendar is the 1st

In VBA you can do this:

Dim i As Integer
Dim s As String

For i = 1 To 12
    s = MonthName(i) & " starts on " & _
    WeekdayName(Weekday(CDate(i & "/01/" & Year(Date))))
    MsgBox s
Next i

And you get:
[tt]
January starts on Friday
February starts on Monday
March starts on Monday
April starts on Thursday
May starts on Saturday
June starts on Tuesday
July starts on Thursday
August starts on Sunday
September starts on Wednesday
October starts on Friday
November starts on Monday
December starts on Wednesday[/tt]

Have fun.

---- Andy

 

[may1hem]

Thanks for your help Andy, but I don't want to use VBA. I want it to be done by formulas on the spreadsheet.

How can this be done?

 

[Gavona]

Have a look at the Weekday function

Gavin

 

[Gavona]

Try: In Monday Week1: =$A$1-WEEKDAY(A1,2)+1



Gavin

 

[SkipVought]

Here's a method:

Set up a table of first of the month dates, like...
[tt]
DTE MO DOW
1/1/2010 1 Fri
2/1/2010 2 Mon
3/1/2010 3 Mon
[/tt]
NAME these ranges.

On your calendar sheet...
[tt]
YR 2010
MO 1

Mon =IF(INDEX(DOW, MATCH(B$2,MO,0),1)=A4,1,IF(B3="","",B3+1))
Tue
Wed
Thu
Fri
[/tt]
Then subsequent COLUMNS on a row will be...
[tt]
=If(CellToLeft+7>day(date($B$1,$B$2+1,0)),"",CellToLeft+7)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Some corrections:

1. your first 2 columns must go thru 7 days (Mon - Sun)

2. change the formula in columns 3 and following...
[tt]
=IF($B$10+ROW()-3+(COLUMN()-3)*7>DAY(DATE($B$1,$B$2+1,0)),"",$B$10+ROW()-3+(COLUMN()-3)*7)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Another solution: J. Walkenbach presents one array formula with entry date on his The Spreadsheet Page.

combo