=EDATE(A2,LEFT(B2,2)*12)

My formula is taking advantage of the fact that your number is separated
from your text ("yr") by a space. The LEFT function (with 2 as the second
argument) will grab the leftmost two characters from the text. If that is a
two-digit number, then those two digits are returned; if that is a one-digit
number, then that digit with a space after it is returned. This number is
next multiplied by 12 to get the number of months represented by that number
of years (which is required for the EDATE function which works with months,
not years). As it turns out, Excel goes out of its way to aid you in
performing calculations with numbers when it can. One of those ways it helps
is to ignore the trailing space when multiply a real number times a text
string consisting of a digit followed by any number of spaces (it
effectively performs the equivalent of applying the TRIM function to the
text and then converting the text number to a real number before performing
the calculation). To see this, put this example formula in a cell and you
will see Excel TRIMs off the leading and trailing spaces, converts the text
number to a real number and performs the math...

=" 123 "*3

--
Rick (MVP - Excel)


"SCrowley" wrote in message
...
Can you break it down and tell me what each step is doing? Thank you. I'm
trying to understand formula logic so that I too may be of help to others
someday.
--
Thank you,

scrowley(AT)littleonline.com


"Rick Rothstein" wrote:

Of course, if the OP did want to go past 9 years, this modification to
your
formula would allow up to 99 years...

=EDATE(A2,LEFT(B2,2)*12)

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try this:

=EDATE(A2,LEFT(B2)*12)

Assuming you don't project beyond 9 yrs.

--
Biff
Microsoft Excel MVP


"SCrowley" wrote in message
...
Hi,

I've looked in this forum and the help feature in Excel and have only
partially succeeded.

I'm trying to return an Warranty Expiry Date based on date of receipt
of
product.

Cell A2 has Date 9/06/08
Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc.

Here are the two formulas I've played with:
=IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24)))
=IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48}))

they work great if I have "1 yr" selected, it calculates 12 additional
months and returns 9/06/09, but returns FALSE if "2 yr" or higher is
selected.

I know I've missed it by just a comma or something. Any suggestions
are
welcome.





--
Thank you,

scrowley(AT)littleonline.com