Using Year Month Day formula and the old blank cell chestnut

[Meady16v]

Hi,

I wonder if someone can help me. I need to know how over due something is based on TODAY() from say column K but, to ignore this if, column R is populated. I've got the first bit sorted ok, it's the second part that I'm having trouble with. My formula is below, I cant work in the additional cell reference:

=IFERROR(IF(DATEDIF(K2,TODAY(),"y") = 0, "", DATEDIF(K2,TODAY(),"y") &" year(s), ")& IF(DATEDIF(K2,TODAY(),"ym") = 0, "", DATEDIF(K2,TODAY(),"ym") &" month(s), ")& IF(DATEDIF(K2,TODAY(),"md") = 0,"",DATEDIF(K2,TODAY(),"md")&" Days"),"")

Also, is there a way of tidying this up or shortening it?

The old blank cell chestnut:
I have the blank cell = 0 issue. Some of my calculations this works and if the referenced cell is blank, the cell with the formula in returns blank. However, I use the same calculation referencing different cells and I get the 0 even though I'm telling it not to. Below is my formula;

=IF(ISTEXT(G19),INDEX('Property List'!X:X,MATCH('Reports Current Programme'!G19,'Property List'!B:B,0)),"")

column X is populated with eitther "yes" "No" or no data, the no data cells are return 0 and not being left blank.

Any help would be much appreciated.

 

[Chameleon64]

For the first formula you could check against column R with the following (I'm afraid I haven't been able to shorten it):

=IFERROR(IF(DATEDIF(K2,TODAY(),"y")=0,"",IF(COUNTA($R:$R)=0,(DATEDIF(K2,TODAY(),"y")&" year(s), ")&IF(DATEDIF(K2,TODAY(),"ym")=0,"",DATEDIF(K2,TODAY(),"ym")&" month(s), ")&IF(DATEDIF(K2,TODAY(),"md")=0,"",DATEDIF(K2,TODAY(),"md")&" Days"),"")),"")


There are various ways around the blank cell = 0 problem:

1. Use conditional formatting to make the font colour same as background.
2. Untick the "Show a zero in cells that have zero value" box in File/Options/Advanced
3. Use another IF statement around INDEX to produce "" if the INDEX value is zero