Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using an IF function to insert a string into a cell if the cell specified is #N/A, the function works if I use the following

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",AR2,"')"))

where AR2 is the date, here are the two different strings I am being returned

insert in tempTable values('BIXEN V VASILE ALIN GEORGESCU','19/3/2012')
insert in tempTable values('BANEKIOSKEN V MUHAMMAD NASEEB KHAN','41003')

the problem is some of the dates are ok where others are in the date serial number format so I am trying to use the TEXT function to change this on the following function it is not working

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",TEXT(AR2,d/m/yyyy),"')"))

i have also tried

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",IF(AR2=d/m/yyyy,AR2,TEXT(AR2,d/m/yyyy)),"')"))

Does anyone see what I am doing wrong?

EDIT: here is the formula working

=IF(AS2="#N/A","",CONCATENATE("insert in tempTable values('",AQ2,"','",IF(AR2="d/m/yyyy",AR2,TEXT(AR2,"d/m/yyyy")),"')"))
share|improve this question

migrated from Sep 18 '12 at 23:30

This question came from our site for professional and enthusiast programmers.

Surely AR2 won't ever be equal to "d/m/yyyy" (which is what you are checking - it isn't checking the format). If AR2 is a "date" in text format then TEXT function won't change it. I'd try this version =IF(ISNA(AS2),NA(),CONCATENATE("insert in tempTable values('",AQ2,"','",TEXT(AR2,"d/m/yyyy"),"')")) That will also explicitly return #N/A which is "cleaner" than allowing the error, I think – barry houdini Sep 18 '12 at 10:22
up vote 1 down vote accepted

You need quotes around the date format, ie


BTW you can use ISNA(AS2) rather than AS2="#N/A"

Or even (in Excel 2007+)

share|improve this answer
yes this solved it alright, never spotted that at all, cheers... i know i can use the ISNA() function but that removes the #N/A from the cell then when I actually need the #N/A in the cell so i can remove the lines in notepad++ using these #N/A's – flexinIT Sep 18 '12 at 10:10
What I ment was =IF(ISNA(AS2),"",CONCATENATE( .... this has no effect of the source cell AS2 – chris neilsen Sep 18 '12 at 10:23

You must log in to answer this question.