Dynamic Dates

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Thanks again.

Reply | Quote

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

Reply | Quote

Thank you! You’ve been very helpful!

Reply | Quote

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.

Reply | Quote