IF statement with a DATEDIF calculation in it

[Nev Bentley]

Is it possible to add a piece of text to say that there is no end date in the DateDif calculation I have this =IF(DATEDIF(J18,K18,"m"),"No End date") and I get the #VALUE returned

 

[Peter_SSs]

Welcome to the MrExcel board!

Try
=IF(K18="","No end date",DATEDIF(J18,K18,"m"))

 

[Nev Bentley]

Thanks Peter - Now I get the #NUM error

 

[Peter_SSs]

Thanks Peter - Now I get the #NUM error

That would normally indicate that your second date (K18) is less than your first date (J18)

 

[Nev Bentley]

I see the logic to that - But what if I do not have a date in K18? which is why I was trying to put "No end date" in cell L18

 

[Peter_SSs]

But what if I do not have a date in K18? which is why I was trying to put "No end date" in cell L18

Then you use the formula that I suggested.
Here it is working as far as I understand what you are asking.

Book1
	J	K	L
18	5/11/2016		No end date
Datedif

Cell Formulas
Range	Formula
L18	=IF(K18="","No end date",DATEDIF(J18,K18,"m"))

 

[Nev Bentley]

Don't know why it did not work in your previous thread - but it works now - Many thanks for that.

 

[Peter_SSs]

. but it works now - Many thanks for that.

You are welcome.

 

[rjmdc]

so how would i do this?
=DATEDIF([@DOB],TODAY(),"Y") & "." & DATEDIF([@DOB],TODAY(),"YM") & "" this gives me Y.M which is perfect
now i need to add to this to make it one formula if possible,
if the result is <18 ,"needs approval","ok"
how would i do that?

 

[Rick Rothstein]

Your question is not clear. You seem to be asking for the formula to return a Y.M value and also "ok" too. Did you mean for the formula to return either the age warning or the Y.M value? If so, try this...

=IF(DATEDIF([@DOB],TODAY(),"Y")<18,"Needs Approval",DATEDIF([@DOB],TODAY(),"Y") & "." & DATEDIF([@DOB],TODAY(),"YM") & "")

Not sure why you are concatenating the empty text string ("") on the end of your Y.M value, but I left it in because your original formula had it.