Display date serial number using TEXT in IF

Question

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")),"')"))

Answer 1

You need quotes around the date format, ie

TEXT(AR2,"d/m/yyyy")

---

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

Or even (in Excel 2007+)

IFERROR(AS2,CONCATENATE( ... ), "")