converting a numeric string into a date string

Posted by Gavin on May 09, 2001 2:29 AM

Hi,

Can anyone help me please?

I have a numeric string which is actually a date string, but Excel will not recognise it.
Unfortunately the source data cannot be manipulated as it autoformats it into Excel!

The strings are like '200010402' (2 April 2001)

Does anyone know how I can convert them into a proper date format?

Thanking you in advance

Gavin

Posted by Aladin Akyurek on May 09, 2001 3:46 AM

Gavin,

The following should work:

=DATE(RIGHT(A1;2);MID(A1;5;2);LEFT(A1;2))

It delivers the date as Day followed by Month followed by Year.

Note. The formula expects a string of length 8.

Aladin

Posted by Gavin on May 09, 2001 6:43 AM

Hi Aladin

Thanks for the reply, but the formula just gave me an 'invalid' error! The string that A1 referred to was 20010423

Any ideas?

Thanks

Gavin ,

Posted by Gavin on May 09, 2001 6:48 AM

Hi aladin,

Sorry, I've got it now, I have replaced all the " ; " with " , "

Cheers

and thanks again

Gavin