Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Automating a Calendar (Excel 2003)

    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?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating a Calendar (Excel 2003)

    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?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automating a Calendar (Excel 2003)

    How about this?

    Steve
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Automating a Calendar (Excel 2003)

    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
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating a Calendar (Excel 2003)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating a Calendar (Excel 2003)

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

  7. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Automating a Calendar (Excel 2003)

    <P ID="edit" class=small>(Edited by kerryg on 30-Jan-07 13:15. afterthought!)</P>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)
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating a Calendar (Excel 2003)

    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.
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Automating a Calendar (Excel 2003)

    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
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating a Calendar (Excel 2003)

    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, ...

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Automating a Calendar (Excel 2003)

    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 <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating a Calendar (Excel 2003)

    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.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automating a Calendar (Excel 2003)

    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

  14. #14
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Automating a Calendar (Excel 2003)

    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
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating a Calendar (Excel 2003)

    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.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •