Probably very easy question conerning auto-changing dates

[Jacob]

I've got a list of dates in column B. Row 1 is labeled "date", Row 2 starts
my date data (May-04). I want a list in column B of the preceding 23 months,
so columb B ends with Jun-02. Obviously, I can manually input this
information. What I can't figure out is how if I go and change the first
date (say, to Jun-04) it will auto-change my list of dates accordingly (so
my list would now end in Jul-02).

NOTE: I know how to create a handle-fill, but it won't update any changes I
make to the dates, nor can I get it to go backwards, chronologically.

Thanks,
Jacob
(e-mail address removed)

 

[Earl Kiosterud]

Jacob,

The fill handle will go backwards, but only if you drag up or left. Select
the first two, then use it.

Or use this formula, and copy down with fill handle:

=DATE(YEAR(B2),MONTH(B2)-1,DAY(B2))

 

[Jacob]

I think I found the answer to my own question, though I have to believe
there has to be an easier way to accomplish the same thing.
If you put a date into your starting cell B2=(may, 01, 2004), in the
following cells, simply put the folling formula =B2-1, B2-32, B2-63, etc...
You have to enter it into each cell, and it will deduct that amount of days,
effectively changing the date backwards that number of days. Tedious, to say
the least, and it won't hold up forever, because eventually the odd-number
of days in each month will catch up to your -X days and throw off the
months. But it should do for quite a while anyway.
If someone has a more accurate (and faster) method, please post or email
me!!

-Jacob
(e-mail address removed)

 

[Earl Kiosterud]

Jacob,

The formula as written goes into B3. Then copy down with the fill handle.
=DATE(YEAR(B2),MONTH(B2)-1,DAY(B2))