Prev: Excel 2010 Beta Professional
Next: Count the number of columns on row that are not empty within a
From: JLatham on 30 Mar 2010 14:47
Which formula is returning 1/0/1900 - at this point there are 3 or 4 from
myself and Jim Thomlinson.

A date like 1/0/1900 is how Excel formulates a value of 0 as a date. So zero
is being returned and the cell is formatted to display numbers as dates.
Probably need a formula that returns "" vs 0 when A2 is actually blank. See
Jim Thomlinison's (ignor the = in the middle of the formula, a typo on his
part), or us my version:
=IF(ISBLANK(A2),"",IF(ISNUMBER(A2),A2,DATEVALUE(LEFT(A2,10))))
which is pretty much exactly what Jim posted, but without the unwanted =
sign. That would appear to be the formula you want/need.

So either:
=IF(A2 = "", "", IF(ISNUMBER(A2),A2,DATEVALUE(LEFT(A2,10))))
or
=IF(ISBLANK(A2),"",IF(ISNUMBER(A2),A2,DATEVALUE(LEFT(A2,10))))

"Jim" wrote:

> This looks good. I wondering if I'm doing something wrong because the
> formula is returning 1/0/1900 insted of a blank cell.
>
> Any thoughts?
>
> "Jim" wrote:
>
> > Hello,
> >
> > I need a little help changing this formula:
> >
> > =IF(ISNUMBER(A2),A2,DATEVALUE(LEFT(A2,10)))
> >
> > to return a blank if the referencing cell is blank. I appreciate the help.
> >
> > jim
First  |  Prev  | 
Pages: 1 2
Prev: Excel 2010 Beta Professional
Next: Count the number of columns on row that are not empty within a