Automating a Calendar (Excel 2003)

Topic

New Reply

I am trying to make a calendar that I can use for planning. I have attached what I have done so far.

I want to be able to put the starting date of say January 1 2007 and have it all fall into place.

Also I have used conditional formating to shade Saturdays and Sundays and Public Holidays butI cannot figure out how to have the first day of each month start on the appropriate day therefore lining up with the appropriate day on the A column.

Is this possible?

Reply | Quote

Replies

Do you want the 1st of January to be in B3 and to adjust the other months to that, or do you want each row to be a fixed day of the week? If the latter, should row 3 be Sunday, or Monday, or another day?

Reply | Quote

How about this?

Steve

Reply | Quote

Steve this is Great!

I have had a fiddle with the formatting to “pretty” it up. As usual I keep thinking of further enhancements. Is it possible to use the info on the dates worksheet to enter important events and have the event name appear on the calendar leaving the date in place. If I go now to type directly into the cells the date disappears.

Likewise have the holiday name appear on the calendar taking it from the dates worksheet?

Regards Kerry

Reply | Quote

If you type some text in a cell, not only does the date disappear, but you also invalidate the formulas for the remaining days of the month. You cannot have a formula and text in the same cell. You could insert empty rows in between.

BTW do you have Outlook? It might be much more convenient to create a calendar in Outlook. You can easily switch between day, week and month views.

Reply | Quote

I have attached an example. As you see, you lose the compactness.

Reply | Quote

(Edited by kerryg on 30-Jan-07 13:15. afterthought!)

Thanks Hans for your advice. Whoo its starting to look pretty spiffy!

I can see that you are right about the cells losing formula when text is entered. I have fiddled with your version so that to the user they would be unaware of the extra rows inserted. It seems to work fine. I am aware that Outlook provides calendars in various views, but they do not print out in the format I want. I want to be able to keep it as a file in its own right and be able to give it to others who are not too converstant with outlook.

A further question. How can I get a bigger range of background colours in excel?

Also, I know this is probably far fetched but is it possible to somehow colour code the text for dates entered in the dates worksheet (not holidays). I have no idea how it could be done or if even possible?

Kerry

Woops! Also just noted that only one entry can be make for a date (excluding holidays which is not important as we dont have meetings on public holidays)

Reply | Quote

1) Excel uses a palette of 56 colors. You can change the color palette in the Color tab of Tools | Options. Changes are specific to the workbook and will be saved with it.

2) You can add another condition in Conditional Formatting to color the date numbers that have a non-holiday event – see attached version.

3) If you set the cells in column E tro wrap text, you can enter multiple entries in one cell; press Alt+Enter to start a new line. The cells in the calendar should adjust themselves automatically, but Excel is not very good at this, so sometimes you’ll have to adjust the row heights.

Reply | Quote

1) Thanks for the info on the colour pallete- I have learned something new.

2) In regard to the colouring of events – I was thinking more along the line of colouring the text of the name of the event according to a colour selected in the Dates sheet. See attached idea.

3) I would really like to be able to put in multiple events separately and colour them as above but if not then the above wont be possible.

4) there is always the chance that if events are just added one after another I may not realise that I have already used that date and will enter it again and then it wont show on the calendar at all. So is it possible to make a macro that sorts (Dates) Columns D E F into cronological order when ever a new event is entered?

Kerry

Reply | Quote

I fear you’re reinventing the wheel. May I very politely bring Outlook to your attention again? It has a lot of the features you want built in – color coding of events, multiple events per day, various printing options, …

Reply | Quote

I dont doubt Outlook has a lot of features, but I havent yet figured out how to print a year to a page so that all text can be read by the user. I dont believe there are the printing controls to do this. Please prove me wrong.

Also how could I give this calendar as a file to another user? If Outlook can do all this I will be very happy to use it.

Regards Kerry

Reply | Quote

Outlook doesn’t have built-in support for printing a yearly calendar, but there are add-ins that provide this, e.g. YearView.

If you do a google search for free calendars or planners, you’ll find lots of ready-to-use user-firendly applications.

The problem with the spreadsheet is that allowing for multiple events per day, each with its own color coding, would require a radical redesign, and I don’t know if it’s worth spending so much time on it.

Reply | Quote

You could regain some of the “compactness” by adding columns instead of rows for the data. The Day of the month (the calculated numbers) could be in narrow columns and the lookup text in wider columns. This would not add any rows and only widen it a little.

But I agree with Hans’ recommendation: why reinvent the wheel? There are applications out there to do this sort of thing.

Steve

Reply | Quote

Hi Steve

Yes I agree that there are other options within Outlook now that I have been made aware of them. I will be exploring them. However, I still want to pursue this calendar since I have started now. I will be content not to colour code etc.

I thought about using the column idea and started it (see attachment) but found I lost the effect of Conditional formatting for Public holidays and weekends. Is there a way I could still do that?

Kerry

Reply | Quote

The conditional formatting for the inserted columns must refer to the column to the left (containing the date). See attached version. I adjusted the conditional formatting for one cell, then used the format painter to copy it to the rest of the inserted columns.

Reply | Quote

I know I am probably becoming a pain with my “Calendars” but I just love them!! I have been experimenting with the outlook version but still feel there is a value in working on my other versions.

Using the examples created so far I thought I would try and make this one work the same way only turning it around. In doing so I wanted to achieve the same lining up of the weekends (vertically this time) and block out irrelevant days of the month. I have fiddled and fiddled with the formula =E$3+WEEKDAY($A$4)-WEEKDAY(E$3)-7*(WEEKDAY($A$4)>WEEKDAY(E$3)) but have had no luck. Any help would be appreciated.

Kerry

Reply | Quote

See attached workbook. Both formulas and conditional formatting need to be consistent.

Reply | Quote

I am embarrassed to be back with this,but I have not been able to resolve my problem after many many attempts to work out the problem.

In all my calendars I have used a conditonal format you taught me a long time back, where holidays and weekends are shaded. I cannot get holidays to show in this version. Look at C3 according to the formula this should be shaded as 1/1/07 is defined as a holiday. I have defined the range in the Rates worksheet. What am I not doing right?

When I get this right I can copy the formula to the rest of the cells.

Kerry

Reply | Quote

The definition of Holidays has become messed up.
Select Insert | Name | Define…
Select Holidays in the list.
You’ll see =REF#!$F$3:$F$28 in the Refers to box.
Change this to =Rates!$F$3:$F$28 and click OK.

Reply | Quote

Post deleted by kerryg

Reply | Quote

See attached version. All I did was adjust the holidays name (and remove two unused names).

Reply | Quote

I have it working now. I was defining the range on the rates sheet instead of the calendar one.

Thanks Hans!!

Reply | Quote