Wrong Calculation

[Patcheen]

i keep getting this answer 116 im calculation anniversary's from today's date but the person has not had an anniversary as they are very much alive so it should be leaving the cell blank but instead im getting the calculation 116 which should be blank

my formula is
=IFERROR(DATEDIF(F5,TODAY(),"Y"),"none")

f5 is blank so i should be getting a return of none but its given 116

[Norie]

Excel treats blank cells as 0 and DATEDIF(0, TODAY(), "Y") will not return an error.

You need to check for F5 being blank instead of an error.

=IF(F5="", "none", DATEDIF(F5,TODAY(),"Y"))

[AlKey]

Deleted post

[JohnTopley]

Maybe ...

=IF(F5="","none",DATEDIF(F5,TODAY(),"Y")


F5 blank (0) = 00/01/1900 in Excel

Hence 116 (2016-1900)

[Patcheen]

Deleted post?

[Patcheen]

Excel treats blank cells as 0 and DATEDIF(0, TODAY(), "Y") will not return an error.

You need to check for F5 being blank instead of an error.

=IF(F5="", "none", DATEDIF(F5,TODAY(),"Y"))

Yes F5 is blank except for formula

[Patcheen]

Maybe ...

=IF(F5="","none",DATEDIF(F5,TODAY(),"Y")


F5 blank (0) = 00/01/1900 in Excel

Hence 116 (2016-1900)

Still returns 116

[Jonmo1]

Then F5 is not blank.

Can you post the formula you have in F5 ?
Maybe it's actually 0, but you have 0's hidden via formatting or Advanced Options.

Perhaps try
=IF(F5=0,"none",DATEDIF(F5,TODAY(),"Y")

[Patcheen]

=IFERROR(INDEX('Player Database'!H:H,SMALL(INDEX(('Player Database'!$C$2:$C$5943=$A$1)*ROW('Player Database'!$C$2:$C$5943),0),COUNTIF('Player Database'!$C$2:$C$5943,"<>"&$A$1)+ROWS($5:5))),"")

[JohnTopley]

Then F5 <>"" !!!! Works OK for me.

[Patcheen]

Me too Thank you very much John your a life saver....

[Patcheen]

Then F5 is not blank.

Can you post the formula you have in F5 ?
Maybe it's actually 0, but you have 0's hidden via formatting or Advanced Options.

Perhaps try
=IF(F5=0,"none",DATEDIF(F5,TODAY(),"Y")

Thank you works just as well as john's now to decide which 1