Make a formula read "year month date"

[Marq]

Using Excel 2010

I have a cell, which has a formula (it shows the difference between 2 dates) in it that provides me a result as "Year Month Days"

So for example in cell K3 where the formula is I have 9 years 1 Month 14 days

I need to make a formula in another cell that basically says: "if K3 is greater that 11 years then "Allowed"....if less than 11 years then "Yes"

Can someome please help!

I have current (which is not working): =IF(K3>11,"Allowed","Yes")

what am I doing wrong?

 

[CWatts]

Try this:

=IF(LEFT(K3,FIND(" ",K3,1))+0>11,"Allowed","Yes")

You may want to use >=11 in case it is exactly 11 years or 11 years 1 month.

 

[shg]

You're going about this the hard way.

If K3 calculates the difference between A3 and B3, each containing dates, then

=if(datedif(a3, b3, "y") > 11, "Allowed", "Yes")

 

[HalfAce]

The formula you now have that returns your result as "Year Month Days", does this formula look at 2 other cells, each containing a date?
If so, then the simplest solution might be to not look at the result in K3, but simply subtract one date from the other.
For example, if your dates were in A1 and B1, then a formula like this should work.
=IF(B1-A1>4018,"Allowed","Yes")

This just counts the number of days between your dates.
11 Years = 4018 days.

Does that help? (Or do you need a more confusing formula for some reason that looks at K3?)

 

[dcardno]

I assume you are following up on http://www.mrexcel.com/forum/showthread.php?t=573124.
The formula you are using to return the time between the two dates is returning a string - the comparison is not meanigful. You would be better to use the datedif fromula again, this time testing for whether the difference in years is greater than 11, and using that as the logical test in the IF statement to return "allowed" or "yes."

PS - I just saw the reply from CWatts: I'm not sure if you used the approach that Chip identifies to avoid constructions like "0 years 11 months 7 days" - but if you are the test will provide a false positive for 11 month durations.