covert text to date in excel

  reddwarfcrew 18:18 05 Nov 03
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 03

This is a fiddle but it works

=DATE(A4+100,A3,A2)

  reddwarfcrew 20:28 05 Nov 03

Looks good to me here.

  reddwarfcrew 20:32 05 Nov 03

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 03

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 03

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

  reddwarfcrew 22:10 05 Nov 03

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 03

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 03

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.

Qualcomm Snapdragon 835 benchmarks: Antutu, Geekbench 4, GFXBench and PCMark results

1995-2015: How technology has changed the world in 20 years

This stop-frame animation tells a moving story of domestic violence for Refuge

New iPad 2017 preview: Apple's affordable but underspecced new iPad may appeal to the education…