Hiding an unintended value
 

if I9 does not have a date then J9 read "118 Years, 0 months, 18days and I want it to be blank. Any help would be greatly appreciated. I tried the basics but then just a FALSE reading.

J9 Formula: =IF(K9<"","Placed Out of Service",(DATEDIF(I9,NOW(),"y")&" years, "&DATEDIF(I9,NOW(),"ym")&" months, "&DATEDIF(I9,NOW(),"md")&" days"))

Hiding an unintended value
 

On Thu, 18 Jan 2018 17:52:08 +0000, jshaner69
wrote:


if I9 does not have a date then J9 read "118 Years, 0 months, 18days and
I want it to be blank. Any help would be greatly appreciated. I tried
the basics but then just a FALSE reading.

J9 Formula: =IF(K9<"","Placed Out of Service",(DATEDIF(I9,NOW(),"y")&"
years, "&DATEDIF(I9,NOW(),"ym")&" months, "&DATEDIF(I9,NOW(),"md")&"
days"))

In Excel for Windoze, a cell formatted as type=date and blank will
have a date of ZERO. That is 1/1/1900.

Suggest your expression includes a test for I9=0.

Worksheet function can't judge whether it is a date.
VBA function:
isdate()
When it's a date, it returns true, or it returns false.

Hiding an unintended value
 

Thank You for your help, it is greatly appreciated. I will try this solution and see if I can make it work.

Your worksheet function I'll not modify.
I give you the VBA function to judge if it is a date.
If it is a date, it will return true, otherwise it will return false.
The function DateJudge have 2 parametres
ra is a cell address, iyear is a condition that you can modify when the year =iyear, it can be recognized as a date.

Function DateJudge(ra As Range, iyear As Integer)
DateJudge = False
If IsDate(ra) Then
If Year(ra) = iyear Then DateJudge = True
End If
End Function