covert text to date in excel

  reddwarfcrew 18:18 05 Nov 2003
Locked

I have the text '01 06 00' in cell A1 and want to convert it to the format "dd/mm/yyyy".

Now I have achieved this by:

A1=01 06 00


A2=LEFT(A1,2)


A3=RIGHT(LEFT(A1,5),2)


A4=RIGHT(A1,2)


A5=DATE(A4,A3,A2)

The result is "01/06/1900", but the 1900 should be 2000.

Try as I might, I can't get the 1900 to 2000.

Any ideas?

  VoG II 18:39 05 Nov 2003

This is a fiddle but it works

=DATE(A4+100,A3,A2)

  reddwarfcrew 20:28 05 Nov 2003

Looks good to me here.

  reddwarfcrew 20:32 05 Nov 2003

Quick question, what verison of XL are you on.

I'd been trying at work to do similar things yo your suggestion (I may have even done what you've said), but it wasn't playing ball (xl 97).

Now I'm at home on xl2000 and the things I was trying at work, work fine. So I'm not sure if this will work at work tomorrow. I was adding 100 to the year, but getting 1905 instead of 2000???

  VoG II 22:04 05 Nov 2003

Now on Excel 2003. I'll try it tomorrow at work on Excel 97 if I have time.

Talking only about your example A1 cell - if you select the cell then Ctrl+H (find and replace).

In the top just press the space bar and in the bottom type the / forward slash, finally select the Replace option.

You have now converted the cell to a recognised date format which should be recognised by all versions of Excel (I use XP).

If you have more than 1 cell you can change them all by selecting them prior to the Ctrl+H.

HTH

  wee eddie 22:07 05 Nov 2003

but whats wrong with, Right Click > Format Cells > Date > choose your display format.

  reddwarfcrew 22:10 05 Nov 2003

Unfortuanetly, what I'm attempting is not quite as straight forward as that.

The example was more for the principal behind what I'm doing as opposed to what I've actually got.

VoG:

I've been playing a bit more here and 1905 is 2000 days after 1900 (after rounding), so perhaps its a something related to that.

Could simply be a setting on xl at work.

  VoG II 22:10 05 Nov 2003

P.S. Your formula for the month would be better as

=MID(A1,4,2)

but that's just nit-picking.

  reddwarfcrew 22:14 05 Nov 2003

I had come across that, but not played with it yet.

Basically I'm copy and pasting from a database system and using the various text function (left, right, find, search etc) to pull out specific data. Unfortunately the database system displays dates in vaious formats including 2000106, ie the year is shown as the first *3* digits as well as 01 06 00.

What I gave you was what you asked for.

Please post the remaining examples and I will be quite happy to look at them or alternatively you are more than welcome to post to me direct using the envelope.

Best wishes

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?