subtract a variable no. of mos. from a constant date? in excel

[Dennis]

the variable in each row contains a number of months. The constant is
12/31/06.

Return Value of 10/1/06 if 3 months.
Return Value of 9/1/06 if 4 months.
etc.

Thanks,

Dennis

 

[Roger Govier]

Hi Dennis

With constant date in A1 and Number in B1
=DATE(YEAR(A1),MONTH(A1)-B1+1,1)

 

[Jim Thomlinson]

You can use the formula
=MONTH($A$1-A2)
Where A1 contains 12/31/06 and A2 contains 10/01/06

 

[Jim Thomlinson]

Sorry I might have read you question backwards... Try

=DATE(YEAR($A$1), MONTH($A$1)-A2 + 1, 1)

 

[Pete_UK]

I put the constant date in cell F1, with the numbers 3 and 4
representing number of months in A3 and A4. Try this formula in B3:

=DATE(YEAR(F$1),MONTH(F$1)-A3+1,1)

and copy down as necessary. If you put 15 in A3 you get 10/1/05, which
is consistent with what you asked for.

Hope this helps.

Pete

 

[Dennis]

Thanks everyone for your help on these formulas. It was sucessful!