Auto-incrementing dates

**jomili** · 12-29-2010, 06:46 PM

Okay, here's a weird one:

I have a series of macros that create a projection. During part of the process the user selects dates of history months to use in creating the projection from a userform. The history months available in the listbox need to be ONLY the month ending date for the 12 month period preceding the current month. So for today the range would be:

12/31/2009
1/31/2010
2/28/2010
3/31/2010
4/30/2010
5/31/2010
6/30/2010
7/31/2010
8/31/2010
9/30/2010
10/31/2010
11/30/2010

I have these dates in a named range on my Sheet1, and each month I have to go update the range by adding the newest and deleting the oldest range.

My question is: is there a date formula that I can put in the cells that will provide the dates, formatted as above, but for the current twelve month cycle? In other words, it would look at today's date and make a calculation as to which month it should be showing. I'm sure there's a formula that would do it, but date formulas aren't my strong suit.

Thanks in advance for any help on this.

**Huron** · 12-29-2010, 07:58 PM

You could use

=EOMONTH(TODAY(),ROW(A1)-13)

copied into the twelve cells in your range

You will need to have the Analysis Toolpack installed to use the Eomonth function.

**jomili** · 12-30-2010, 09:35 AM

Huron,

Your formula worked well for me, and I can make sure all of my customers have the Analysis Toolpack installed. But I wonder if you can help explain the formula to me? I tried to research, and here http://excel.tips.net/Pages/T002810_..._Function.html
found this

Please Login or Register  to view this content.

So I can see in your formula "(TODAY()" takes the place of "NOW()" or "A1" from the examples above, and so "ROW(A1)-13" takes the place of the 0 or 6. But I don't understand what the "ROW(A1)-13" is doing. Should there be a value in A1? The formula seems to work without it, but I don't know why.

BTW, thanks for providing it!

**DonkeyOte** · 12-30-2010, 09:46 AM

You can avoid the ATP requirement by simply using:

Assume latest date is in last row:

Please Login or Register  to view this content.

then remaining dates:

Please Login or Register  to view this content.

above not ideal in so far as A1:A11 are looking down for their results but it's a nice short alternative... another approach:

Please Login or Register  to view this content.

**Huron** · 12-30-2010, 10:37 AM

Hi Jomili

The "Row(A1)-13" is just a way of decreasing the argument of the EOMonth function automatically as the formula is copied down the column. Row(A1) is 1, Row(B1) is 2 etc so the formula becomes EOMonth(Today(),1-13) in the first row of your range and EOMonth (Today(),2-13) in the second row etc.

DonkeyOte's suggestion's are probably better as they don't require the Analysis Toolpak.

**jomili** · 12-30-2010, 10:43 AM

DonkeyOte,
I like your second solution, as it's only one formula, so based on Huron's recommendation I'll go with that one.

Huron,

Thanks for the great explantion of the ROW argument. It helps me to understand not only your formula but DonkeyOte's as well.

Thanks for all the help!

Happy New Year!

**DonkeyOte** · 12-30-2010, 11:09 AM

Originally Posted by jomili

Thanks for the great explantion of the ROW argument. It helps me to understand not only your formula but DonkeyOte's as well

FWIW, I prefer ROWS to ROW as the latter is susceptible to error should the source sheet be physically modified in a particular way.

ROW is preferable/necessary where you want to return an Array of values else I always opt for ROWS - same holds true for COLUMN & COLUMNS obviously.