Problem with #error in Date column

[Elina]

Hi all,

I am experiencing a problem which seems so simple but eventually so hard to do.
I have a column with Dates as string. I want to convert this column to date which I managed to do as seen below. Eventually I want to replace the #error with an empty cell. If Date column is formatted as date I am NOT able to replace the error(while if Date column is formatted as text I am able to replace it). Can you please tell me a function I can used so that I have my column as date and error replaced by empty cell?

Date-string Date
20130927 27/09/2013
20130930 30/09/2013
20130930 30/09/2013
#error
20130928 28/09/2013
20130915 15/09/2013

Would really appreciate it if anyone can help me

 

[pbaldy]

You haven't shown your method, but if you're trying to replace with a zero length string (""), try Null instead.

 

[Elina]

Thanks for your reply.

The function I used to convert the Date-string column to Date is the DateSerial. This works perfect apart from the fact that I get an #error in the Date column for the empty cells in the Date-string column. If I used the formula iif(iserror([Date]),””,[Date]) I still see the error value. I even tried iif(isnull([Date]),””,[Date]) but still the error is there.

 

[pbaldy]

In your conversion try this type of thing:

IIf(Nz(DateString, "") = "", Null, DateSerial(...))

 

[namliam]

1) dont call your column date, date is a reserved word which can cause plenty of problems
2) try this:
iif(nz([date-string],"")="","",Dateserial(bla bla))

Edit: *geez* paul

 

[pbaldy]

Point 1 is valid. Point 2, not so much.

 

[Elina]

It works perfectly!!!

Thanks a lot

 

[pbaldy]

Happy to help!