Excel IF AND NOT formula not working 1

[knifey]

Hi,

I'm attempting to calculate the days between two dates (normally easy enough).
My problem is that the columns that contain the two dates can also hold text and so have the general cell format.
Can anyone advise on where I'm going wrong in my formula?

=IF(AND(O1>0, IsDate(O1),NOT(ISTEXT(O1)),M1>0,IsDate(M1),NOT(ISTEXT(M1))),O1-M1,"?")

Thanks,
K

 

[zelgar]

I think you might have a couple of errors. I don't know if IsDate() is a function and I don't think you need the NOT(ISTEXT() part either. Try the following code:

=IF(AND(O1>0, ISNUMBER(O1),N1>0,ISNUMBER(N1)),O1-N1,"?")

 

[knifey]

Thanks Zelgar,
It's now working great.
Cheers,
K
P.S. IsDate() is a vba udf I created

 

[SkipVought]

hi,

My problem is that the columns that contain the two dates can also hold text

Yes, this IS a problem that ought not be. Mixed data in a column is a dreadful travestry.

Is there no way to avoid this bastardization of a table of data? IMNSHO, this is where the effort ought to be directed if at all possible.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}