# ISBLANK used with TODAY() but text in cell gives a #VALUE! error

Copper Contributor

# ISBLANK used with TODAY() but text in cell gives a #VALUE! error

Hi there, I have formula to give a blank if there is not an entry in the cell I am calculating the date on, but my added secondary level is that if the cell contains text rather than a date in it, then it returns #VALUE!

So I have =IF(ISBLANK(E5)," ",E5-TODAY()) which gives a result of 37 - as in number of days left as E5 contains a date 37 days from today

But then I have  =IF(ISBLANK(F5)," ",F5-TODAY()) which gives a result of  #VALUE! and this is because cell F5 contains text

I assume it needs to be something like either AND OR ISERROR ISNUMBER combined with the IF ISBLANK above, but my brain cannot compute what it needs and neither can searching for an answer as I probably am not asking for the correct thing!

4 Replies

# Re: ISBLANK used with TODAY() but text in cell gives a #VALUE! error

Try and substitute ISBLANK() with ISTEXT().

# Re: ISBLANK used with TODAY() but text in cell gives a #VALUE! error

@Detlef_Lewin Hi there and thank you, this does remove the #VALUE! error which is great, but because there is no ISBLANK now I have the problem of any blank cells returning the value of -45499.

So perhaps it's a combination of ISBLANK AND ISTEXT, so now I need to work out if it's an OR within the IF or something like that...

Brilliant!! With your help of ISTEXT and getting the parenthesis in the correct places(!!) I have ended up with the below so I thought I would post in order to help others

=IF(OR(ISTEXT(E3),ISBLANK(E3))," ",E3-TODAY())

# Re: ISBLANK used with TODAY() but text in cell gives a #VALUE! error

Or simpler.

``=IF(ISNUMBER(E5),E5-TODAY(),"")``

# Re: ISBLANK used with TODAY() but text in cell gives a #VALUE! error

Yes! Thank you that works also, much appreciated!