Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Updating calendars from year to year (Excel 2003)

    Hi loungers....this is probably a much more simpler problem than I conceive it to be, but here goes....each year, I make a calendar showing all workdays. Certain events happen on certain days (eg: 1st Mon of each month; 1st & 3rd Wed of each month; 2nd & 4th Fri of each month etc...see attachment). The cycle is the same each year, but the actual dates differ from one year to the next...presently, I re-do the calendar by entering Jan 1, 200? in the first row, right-clicking & filling down, and selecting "Workdays"....then, I apply a filter to the 'day' column, select the day (eg: Wed) and enter the event on the appropriate Wed of each month...I do this again, filtering for Mon..or Wed...or Fri...whatever the correct day is.

    My sample is a calendar for 2009; isn't there some way to re-set the days & dates for the new year (eg: 2010) and recreate a 2010 calendar & maintain the same cycle or rhythm without having to do all the filtering for each day throughout the year? Thanks for any suggestions
    Attached Files Attached Files

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

    Re: Updating calendars from year to year (Excel 2003)

    Why is January 2, 2009 the second Friday of the month? (Because it's Friday the 2nd? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Updating calendars from year to year (Excel 20

    ...oops...my mistake, Hans...it should be Jan 9 as the second Fri....the rhythm in my sample is fictitious, b/c the regular rhythm of events would have been too numerous for the file to be under 100kb, and also, I might make a different calendar with other events....I am trying to understand or learn if there is a way to replicate the rhythm using a formula (maybe DATE ?) instead of just filling down the workdays and workdates......

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

    Re: Updating calendars from year to year (Excel 20

    The attached workbook will mostly show #NAME when you open it.
    1) Make sure that the Analysis ToolPak add-in has been installed i.e. its check box is ticked in Tools | Add-Ins...
    2) Edit one of the formulas and press Enter without changing anything. This should recalculate the formulas.

    If you change the year in B1, the dates and events should be updated automatically.
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Updating calendars from year to year (Excel 20

    ...???....thank you, Hans, but I don't understand how it is supposed to work...Analysis ToolPak was installed; I edited, but didn't change anything; I changed date in B1 and the day & date in A6 & B6 changed, but nothing else changed...I gather that I am supposed to enter events (?) but don't know in which column and wonder if I am missing a column?....everything stays at #NAME?

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

    Re: Updating calendars from year to year (Excel 20

    This is one of Excel's more stupid features - it automatically translates worksheet functions to and from the language of the user, but it doesn't do that for functions from the Analysis ToolPak. So when I entered WORKDAY in my Dutch-language version of Excel, it wasn't recognized, causing the formulas to return #NAME.

    Try the following:
    - Select Edit | Replace...
    - Enter WORKDAY in both the "Find what" and "Replace with" boxes.
    - Click "Replace All".

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Updating calendars from year to year (Excel 20

    ........got it...I guess if I knew more Dutch than Hans or Heineken I might have thought of that....(not likely, at my level of Excel)...thank you...seems to be working fine. I juts have to spend some time studying the formula so that I can adapt it to my real-life workbook....if I have any questions, I will come back...and I will let you know how if functions in any event...thanks again.

Posting Permissions

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