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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Dennis

With constant date in A1 and Number in B1
=DATE(YEAR(A1),MONTH(A1)-B1+1,1)
 
You can use the formula
=MONTH($A$1-A2)
Where A1 contains 12/31/06 and A2 contains 10/01/06
 
Sorry I might have read you question backwards... Try

=DATE(YEAR($A$1), MONTH($A$1)-A2 + 1, 1)
 
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
 
Back
Top