Is Error Help on a formula
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 |
Is Error Help on a formula
=IF(ISBLANK(A2),A2,IF(ISNUMBER(A2),A2,DATEVALUE(LE FT(A2,10))))
"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 |
Is Error Help on a formula
=if(A2 = "', "", =IF(ISNUMBER(A2),A2,DATEVALUE(LEFT(A2,10))))
-- HTH... Jim Thomlinson "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 |
Is Error Help on a formula
A couple of other options. The previous formula will show a 0 if A2 is
blank. If you don't want to see a zero then, modify it just a tad: =IF(ISBLANK(A2),"",IF(ISNUMBER(A2),A2,DATEVALUE(LE FT(A2,10)))) or if you don't mind seeing a zero when A2 is blank, then we can tighten it up a little this way: =IF(OR(ISBLANK(A2),ISNUMBER(A2)),A2,DATEVALUE(LEFT (A2,10))) "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 |
Is Error Help on a formula
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 |
Is Error Help on a formula
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(LE FT(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(LE FT(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 |
All times are GMT +1. The time now is 08:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com