Date format to Number format

[mahershams]

Dear all

I have convert the date format 20040106. But when I use this date to create a ID, it shows the value 37992 instead of 20040106. Even if I copy and paste this as number format, it still shows the value 37992. How to solve this?

Thanks in advance.

Regards
Shams

[martindwilson]

37992 is the date code for jan 6 2004
wrap whatever formula you used to convert the date with in text()
=text(your formula,"yyyymmdd")

[mahershams]

Hi

Thanks for suggestion. In K column it shows the 37992. How to convert it as text format as you said.

[mahershams]

Dear Martin

Thanks for solution. Please see the excel file in K column. How to get it done according to your advice using text format.

Regards
Shams

[martindwilson]

=TEXT(G2,"000000")&H2&TEXT(E2,"yyyymmdd") the first part may be needed as text in case your "Acquiror
DatastreamvCode" is in the format 009761 or just 9761 it will pad it out to 6 digits

[mahershams]

Thank you very much.