Retain date format for input entered as date

Hi all,

I'm using nested SUBSTITUTE formula to make some changes to the some of the data. I'm also using IFERROR to return the input if it does not find the criteria I have specified. I have lot of variations in my input data.

The formula works fine for all except for date format input.

Sample:

N90232Y09--->SUBSTITUTE(A1,"N","")-->90232Y09
12335--->12335
12/3/1923---->58936 (I want the date to be retained)

Can somebody help me out on this and also let me know why the date format is changed even if don't specify any changes for it. I expect my IFERROR to just retain the input as it is.

Thanks,

Chippi

Try setting the date format at the start of your code.

Change the xxxx for wherever the text is (i.e. my data was in Userform1.Label1.Text = Format(Userform1.Label1.Text, "d mmmm yyyy") This gives me 9 Mar 2014.

xxxx.Text = Format(xxxx.Text, "dd mmm yyyy")

Hi ScOttle,

Can you suggest me another method as I'm not using Vb.

Thanks,
Chippi

Apologies, I thought this was the VB forum

Pl see attached file.