Can't Get Rid of #VALUE!
 

I have this formula in Cell AL13 and it is used to translate text into date
format from a mainframe. I tried other methods by formatting the cell into
text, general, etcc... and it's frustrating, so I came across this formula
below and it works great.

=DATE(RIGHT(AK13,4),LEFT(AK13,IF(LEN(AK13) = 8,2,1)),LEFT(RIGHT(AK13,6),2))

The problem is when there is no data to work on the cell displays #VALUE!.

How can I get rid of #VALUE! from being displayed?

I did try working on the next column Cell AM13 with this formula and I get
the same #VALUE! on the screen.

=IF(AL13="","",AL13)

Thank you!

Can't Get Rid of #VALUE!
 

In article ,
NFL wrote:

I have this formula in Cell AL13 and it is used to translate text into date
format from a mainframe. I tried other methods by formatting the cell into
text, general, etcc... and it's frustrating, so I came across this formula
below and it works great.

=DATE(RIGHT(AK13,4),LEFT(AK13,IF(LEN(AK13) = 8,2,1)),LEFT(RIGHT(AK13,6),2))

The problem is when there is no data to work on the cell displays #VALUE!.

How can I get rid of #VALUE! from being displayed?

I did try working on the next column Cell AM13 with this formula and I get
the same #VALUE! on the screen.

=IF(AL13="","",AL13)

Thank you!


Try...

=IF(AK13<"",DATE(RIGHT(AK13,4),LEFT(AK13,IF(LEN(A K13) =
8,2,1)),LEFT(RIGHT(AK13,6),2)),"")

--
Domenic
http://www.xl-central.com

Can't Get Rid of #VALUE!
 

Domenic:

Thank you sooo much! It works great!!

Take care,

"Domenic" wrote:

In article ,
NFL wrote:

I have this formula in Cell AL13 and it is used to translate text into date
format from a mainframe. I tried other methods by formatting the cell into
text, general, etcc... and it's frustrating, so I came across this formula
below and it works great.

=DATE(RIGHT(AK13,4),LEFT(AK13,IF(LEN(AK13) = 8,2,1)),LEFT(RIGHT(AK13,6),2))

The problem is when there is no data to work on the cell displays #VALUE!.

How can I get rid of #VALUE! from being displayed?

I did try working on the next column Cell AM13 with this formula and I get
the same #VALUE! on the screen.

=IF(AL13="","",AL13)

Thank you!


Try...

=IF(AK13<"",DATE(RIGHT(AK13,4),LEFT(AK13,IF(LEN(A K13) =
8,2,1)),LEFT(RIGHT(AK13,6),2)),"")

--
Domenic
http://www.xl-central.com