Issue with balnk cells.....

Hi

I have a formula in a cell which brings back a certain date.
However the referenced cells maybe blank as such my initial formula will bring back 00/01/1900. I did struggle trying to find a way for excel not show this figure and found the following works.

custom format:
dd/mm/yyyy;;
( note the 2 ;; )

this will leave the cells blank and me happy.


Yet.

in a subsequent cell I am using this cell with the blank as a reference and using the following formula:

=IF(ISBLANK(R30),"NA",IF(ISBLANK(L30),"NA",R30-L30))

The issue I not face is that this formula above does not see that R30 is blank,
whilst it looks blank it really show 00/01/1900.

the result of the formula above is -40694 which looks like a date in number format.
I want to ensure that if either R30 or L30 are blank then a NA is returned. otherwise to perform the r30 - l30.


thanks

Hi

This does work!
However I have noticed that if both cells are blank (contain the 00/01/1900), the a #Value occurs.

Would I need to enter in a iserror?

agreed, cant seem to get aroudn this error currently.
Still coming up with #Value

I think this is why I started with the Isblank function.

I did just try the iserror as well and it still comes up

now it works, have changed it r30=""

for some reason why I saved the previous file and reopened, the 00/01/1900 values all turned in proper blanks, no need to change format.

I am perplexed!

the issue is....

some cells are real Blanks as in ""

others are 00/01/1900

the formula is unable to deal with "", it can only deal with 00/01/1900

so i need to amend formula so R30<1 or ""

this works!

=IF(OR(R162="",R162<1),"NA",IF(OR(L162="",L162<1),"NA",R162-L162))

thanks once again!