| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stopping a constant variable cell from being copied. | New Users to Excel | |||
| How to average one constant # to variable #s? A1:A2 A1:A3 A1:A4 | Excel Worksheet Functions | |||
| Formula to subtract a date from current date | Excel Worksheet Functions | |||
| How do I subtract a date from a date for a sum of total years? | Excel Discussion (Misc queries) | |||
| calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions | |||
Linear Mode
