| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
