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

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

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

Hi Dennis

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

Roger Govier


"Dennis" wrote in message
...
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

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

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

Jim Thomlinson


"Dennis" wrote:

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

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

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

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

Jim Thomlinson


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Dennis" wrote:

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

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

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

On Jul 13, 11:06 pm, Dennis wrote:
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

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

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

"Dennis" wrote:

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