Creating a Calendar

[drshkd]

I have an excel file in which there are 12 sheets. Each sheet contains the calendar for a particular month. There are 35 cells in the block (5 Rows/weeks X 7 Columns/days) with name of the day on header row. Now I have to manually enter the first dates of each month with corresponding day & then put the formula "Cell+1" for the rest of the month to arrange the dates. Now for the next year I have to arrange it all again. So is there any way that I have to only enter the first day of the year in the correct weekday & the rest of the months in rest of the sheets are arranged automatically. The extra cells in each month must remain blank.
Thanks

 

[Marcelo Branco]

There are templates in the Internet e.g
https://templates.office.com/en-us/12-Month-Calendar-TM10000102

M.

 

[Eric W]

Let's say you have the year in Sheet1!A1, and the number of the month is in A2 on each sheet. Now on each sheet select your entire 35 cell block, enter this formula:

=IFERROR(DAY(TEXT(DATE(Sheet1!A1,A2,1),"yyyy-mm-")&{2,3,4,5,6,7,8}+{0;7;14;21;28;35}-WEEKDAY(DATE(Sheet1!A1,A2,1))),"")

and press Control+Shift+Enter. Now every sheet automatically updates when you change the year.

 

[drshkd]

Thanks to both Marcelo Branco & Eric W for the quick reply. Actually I couldn't find any template earlier. Thanks Marcelo Branco for this slick calendar template from Microsoft Office. Also Thanks to Eric W for a nice formula to work around. I also thank the Mr.Excel forum for helping me out again this time. Thanks