Adding dates in a IF Statement

[Inchman]

OK, I need to use an IF statement to add a certain amount of time to
date.
Example..
Date cell is K4
IF hurdle is L4
formula is in M4

The way it needs to work is, if L4 = "Yes" then I need to add 7 year
and 9 months to the date in K4. If not, I need it to add 9 years and
months to the date in K4.

Everything I try comes out with a "#VALUE!" error in the cell.
Here is one of the ways I tried it...
=IF(L4="Yes",K4+2465,K4+3560)

Note, K4 and M4 are both formatted as dates in the same configuration.
Any help would be awesome

 

[Gord Dibben]

Should work........I tested and OK..........if the Date in K4 is a
real date.

If you format K4 to General what do you see in the cell?

Should be a 5 digit number.

February 15, 2012 will be 40954

Might be your date is text?

Re-enter.


Gord

 

[joeu2004]

Date cell is K4 [....]
Everything I try comes out with a "#VALUE!" error in the cell.
Here is one of the ways I tried it...
=IF(L4="Yes",K4+2465,K4+3560)
Note, K4 and M4 are both formatted as dates in the same configuration.

The problem is: Excel does not recognize the form of your dates.
Therefore, it treats them as text. The format does not matter (well, as
long as it is not Text).

For example, on my computer, Excel recognizes 2/15/2012 as today's date. If
I enter 15/2/2012, Excel treats that as text.

The Regional and Language control panel determines what Excel recognizes as
a date.

Alternatively, you can use the DATE function to enter dates unambiguously.
For example, DATE(2012,2,15).

if L4 = "Yes" then I need to add 7 years
and 9 months to the date in K4. If not, I need it to
add 9 years and 9 months to the date in K4.

I don't know on what planet 2465 is 7 years 9 months, but I am pretty sure
that is not Earth time . Even 3560 is dubious. (I have not tried all
possible interpretations.)

I suggest that you use DATE(7+YEAR(K4),7+MONTH(K4),DAY(K4)).

Of course, that presumes that you fix your problem with K4 first.