Create automatic calendar

**joeljoel** · 11-16-2010, 04:07 PM

I am trying to develop a calendar that automatically populates itself with dates and non weekend days each month also being numbered. Attached is the spreadsheet. The big issue is getting the dates to stop at the right time 28, 39, 30 or 31. It works fine now, except for February. I can't seem to get the additional nested IF statements in there to include for Feb on both leap and non-leap years. I know only need the formula in Row 38, as the last week is the only one that is an issue. I have been trying this for a couple days now and keep getting errors. Any help would be appreciated.

Also, if I'm attacking this completely wrong, I'd be open to listening to that too. Is there a simpler way to create this calendar??

Thanks for you help!!

**mrice** · 11-16-2010, 05:53 PM

You could use a macro approach. e.g.

Please Login or Register  to view this content.

This should deal with leap years. You will need to have the same number of rows per week - you seem to have 7 for NOV and 8 for DEC.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**outofthehat** · 11-16-2010, 06:12 PM

Do you know, this is something I've been meaning to set up for ages!

Take a look at the attached. It uses the inputs in the top to calculate the first day of the first line AS A DATE. I've separated out the calculation using WEEKDAY, and the calculation of the first day of the chosen month, so you can see what's going on. The dates are all then formatted with custom format d, which just puts the day number in, but the joy is, you just need to add one to it each time. I've also added in conditional formatting to turn any dates outside the current month grey...

NB Your profile says Excel 2003, but you've attached a 2007 book - please could you update your profile so people can help you more appropriately.

**joeljoel** · 11-16-2010, 06:59 PM

Thanks Martin and outofthehat for your quick replies. Both great ideas. I can quickly see that outofthehat's suggestion seems to work, if I can then get the formatting the way I want it, which I think I can do. But I want to explore Martin's idea, too. I haven't done any macros, so I'll be swimming in dangerous waters - but it might be a good time to try.

Profile updated, outofthehat - thanks for the reminder!

I appreciate immensely the sharing the sharing of knowledge here!!

**outofthehat** · 11-17-2010, 03:23 AM

Improvement - make that top-left cell of the calendar

Please Login or Register  to view this content.

Gets rid of the unnecessary IF...

**Kronikcold** · 03-05-2014, 11:39 PM

Trying to figure something out
Will post if I can come up either something

Create automatic calendar

LinkBack

Thread Tools

Rate This Thread

Display

Create automatic calendar

Re: Help creating automatic Calendar

Re: Help creating automatic Calendar

Re: Help creating automatic Calendar

Re: Create automatic calendar

Re: Create automatic calendar

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions