calculate date by subtracting months

[keith0628]

I have a date, (6/11/04 for example) and I want to calculate a new dat
plus or minus a certain number of months from my known date. So th
calculated field would be my known date minus six months. How do I d
this?

Keit

 

[Norman Harker]

Hi Keith!

As far as a general solution is concerned, I'd base this on the
following:

=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+AddMons+1,0))))

Where AddMons is the number of months to be added or subtracted.


Peter Dorigo, produced a more efficient form:

=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))

There are simpler formulas but they don't tend to be very robust
because of the problem of not being able to define a month. The
absolute references in both formulas facilitate copy the formula down
or across to generate a series the same number of months apart.

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/datearith.htm