# Thread: Dynamic Dates

1. ## 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. ## 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

3. ## 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. ## 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. ## 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. ## Re: Dynamic Dates

Well, mumble grumble, I completely misread your post topic, and my memory on using =index was wrong. See attached.

7. ## Re: Dynamic Dates

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

8. ## 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.

#### Posting Permissions

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