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  |