If Array EDATE formula help

[SCrowley]

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.

 

[T. Valko]

Try this:

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

Assuming you don't project beyond 9 yrs.

 

[Rick Rothstein]

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)

Try this:

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

Assuming you don't project beyond 9 yrs.

 

[T. Valko]

To shed some light on your actual problem:

=IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24)))

returns FALSE if "2 yr" or higher is selected.

That means what you *see* in B2 does not equal "2 yr".

There may be unseen characters like leading/trailing spaces:
<space>2 yr
2 yr<space>
<space>2 yr<space>

--
Biff
Microsoft Excel MVP

Try this:

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

Assuming you don't project beyond 9 yrs.

 

[SCrowley]

THANK YOU VERY MUCH!!! I love this forum!
--
Thank you,

scrowley(AT)littleonline.com

To shed some light on your actual problem:

=IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24)))

returns FALSE if "2 yr" or higher is selected.

That means what you *see* in B2 does not equal "2 yr".

There may be unseen characters like leading/trailing spaces:
<space>2 yr
2 yr<space>
<space>2 yr<space>

 

[SCrowley]

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

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 Rothstein]

=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

 

[T. Valko]

You're welcome. Thanks for the feedback!