Results 1 to 8 of 8

Thread: Dynamic Dates

  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Dates

    I need to create an Excel report that shows a moving average for the last 12 months. I would like my column headings (Month/Year) to be dynamic based on the current month. For instance if the current period is March 2001 my 12 columns need to be April 2000, May 2000, June 2000...to March 2001 but if the current period is June 2000 my column headings need to be July 2000 to June 2001.

    I thought of using nested IF statements but you can only use 7.

    Does anyone have any suggestions?

    Thanks,

    Christa

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Dynamic Dates

    In the past I've done this using =INDEX(reference,row_num,column_num,area_num) where the row_num and/or column_num locators are driven by the 12 month moving average begining and ending date ranges, offset from the most recent report month
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Dates

    Thanks,

    I'll give that a try. I haven't used the INDEX reference style formula before only the array style...so any additional insight you could provide would be helpful.

    Thanks again

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Dynamic Dates

    If you have TODAY() in A1, then in B1 enter =A1+(EOMONTH(A1,0)-A1)+1, and drag ut across to L1. Format the cells as "mmmm" and you should have each column headed by consecutive months, starting with the current month. The formula requires that you have the Analysis toolpak installed, but if you do not you can replace it with =A1+((DATE(YEAR(A1),MONTH(A1)+1,0))-A1)+1. This updates so that on May 1st the first heading will be May.

    Andrew C

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Dates

    Thank You! I actually just stumbled onto "EOMONTH" in my wanderings thru Excel help...this gives me a big head start.

    Thanks again.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Dynamic Dates

    Well, mumble grumble, I completely misread your post topic, and my memory on using =index was wrong. See attached.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Dates

    Thank you! You've been very helpful! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Dynamic Dates

    That helps me feel better! As you see, I didn't use dynamic dates to derive moving average. Also, if you need to get a true mean, the formulas get a little trickier.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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