Why is "" always greater in value?

[niall777]

I have a query where returning an IF statement with "" this "" seems to hold a greater value when using that value to compare 2 dates. In my test Excel file I've demonstrated how Cell D2 displays TRUE to the IF statement result of "" being greater than A2. I wonder if anyone can explain exactly how Excel treats the "" as despite the Cell value being blank its clearly always trumping any value being matched against it, I'd like to understand why Excel reacts this way, what exactly does "" represent if not blank? Is there an alternative to "" to have an IF statement to return the cell being blank and additionally holding no value?

Thanks!

[niall777]

My current theory would be that "" equals a blank text value, so perhaps a text value even blank is greater no matter what? and that maybe "" should be replaced with 01/01/1900 and some conditional formatting to hide it? Any other solutions or explanations appreciated.

[FDibbins]

yes a text entry is always greater than any numeric entry

[Aardigspook]

If you need the cell to display as empty but evaluate as less than other (positive) numbers, try using =IF(condition,something,0) instead of =IF(condition,something,""), then apply a custom format to it of 0;-0; (zero semicolon minus-zero semicolon). That will show any number normally unless it's zero, when it will show as blank.

Hope that makes sense and is maybe of some help.

ps Welcome to the forum.

[niall777]

Thanks for confirming my thoughts. I will go with the 01/01/1900 or a 0 and conditional formatting to resolve as this seems a good workable solution.

[FDibbins]

Happy to help, thanks for the feedback

[Aardigspook]

Glad to help, thanks for the feedback and the rep.