Prev: Excel 2010 Beta Professional
Next: Count the number of columns on row that are not empty within a
From: Jim on 30 Mar 2010 14:11
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
From: JLatham on 30 Mar 2010 14:18
=IF(ISBLANK(A2),A2,IF(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
From: Jim Thomlinson on 30 Mar 2010 14:18
=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
From: JLatham on 30 Mar 2010 14:29
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(LEFT(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
From: Jim on 30 Mar 2010 14:32
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
 |  Next  |  Last
Pages: 1 2
Prev: Excel 2010 Beta Professional
Next: Count the number of columns on row that are not empty within a