Need formula for progressive dates within a worksheet

[Patricia]

example:

Nov-03 9/29/03 Nov-02 9/30/02 Nov-01 10/1/01

(lots of data in columns)

Dec-03 9/29/03 Dec-02 9/30/02 Dec-01 10/1/01

(lots of data in columns)

Jan-04 9/29/03 Jan-03 9/30/02 Jan-02 10/1/01

I need a formula to automaticaly update all the following
dates, when the first date is updated. I know it can be
done, because I am told that "it worked before". This
spreadsheet contains several months worth of data and is
updated monthly.

 

[Bernie Deitrick]

Patricia,

For example, with Nov-03 in cell A1 and 9/29/03 in cell B1, you can use two
formulas.

In cell C1, use the formula

=DATE(YEAR(A2)-1,MONTH(A2),1)
and format custom using mmm-yy

In cell D1, use the formula

=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2)+1)
and format using m/d/y

There was no apparent logical link between the first and second date.....

HTH,
Bernie
Excel MVP