Subtracting Dates

Hello,

I need help subtracting dates in excel. The worksheet I am working with needs all dates to be the last day of the month (for example, 7/31/10).

I want to 7/31/10 at the top of the worksheet, and then have excel automatically deduct one month from that date continuously for 24 months. I have tried several formulas, but they always get messed up because of the differing amounts of days in each month. I have figured out how to do this if I use the first day of the month instead of the last, but for this specific spreadsheet, I need the end of the month.

The formula I have been toying around with is:
=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))

But, this is what I get:
7/31/10
7/1/10
6/1/10
5/1/10
4/1/10
3/1/10
2/1/10
1/1/10... and so on.

What I want is:
7/31/10
6/30/10
5/31/10
4/30/10
3/31/10
2/28/10
1/31/10
12/31/10 ... and so on

Thanks so much in advance!

Try:

=DATE(YEAR(A1),MONTH(A1),0)

copied down.

or if you have the Analysis Toolpak Functions available.

=EOMONTH(A1,-1)