How to get rid of #N/A?

[FrankBooth]

Hi,

I have the formula below for formatting certain dates in a cell,
however the formula posts an #N/A error when it hits an already
properly formatted date.

=IF(ISBLANK(L20106)=TRUE,"",MID(L20106,8,4)&TEXT(MATCH(MID(L20106,1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),"00")&TEXT((MID(L20106,5,2)),"00"))

I would either like to expand the formula to catch the error and not
post #N/A or delete all the #N/A's through some menu command or
procedure.

Any help is appreciated.

Thanks,
--FB

 

[Bob Phillips]

Frank,

You could use conditional formatting to hide it. For instance, if the
formula is in A1, use FormulaIs and a formula of =ISNA(A1), select the
Format button and set the font colour to white.

 

[Daniel.M]

Hi Frank,

Maybe the formula could be changed as well (returning the Date when already
a numeric value).
Just a suggestion.

Assuming a defined name called MList with your months like:
={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}

=IF(L20106="","",IF(ISNUMBER(L20106),L20106,MID(L20106,8,4)&
MATCH(MID(L20106,1,3),MList,0)&MID(L20106,5,2)))

Regards,

Daniel M.

 

[FrankBooth]

Yes! I just inserted my entire formula (without using the defined
name)into the nested IF's False result parameter et voila!

Thanks!