Can't Get Rid of #VALUE!

N

NFL

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!
 
D

Domenic

NFL said:
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!
Click to expand...


Try...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Getting rid of grey background from text copied from internet 1
Get rid of Microsoft AutoUpdate 0
I need to cut characters off of a list of text cells... 2
Word macro that can call cells out of Excel for a find and replace 0
LISTNUM + Mail Merge + INCLUDETEXT : Referencing Paragraph # Can't get to work 0
Finding the proper style field and the numbered paragraph value within the style 0

Top