Automatically convert Text with 2-digit year

[juniper810]

Hi, I'm obtaining dates from another column using =left(a2,8). (This column
contains text following a date.) After I copy and paste special so that the
values are displayed and not the formula, some dates are displayed as "Text
with 2-digit year." This format does not allow these cells to be included
when I filter. I know that I can convert each one manually, by clicking into
each cell, or using "Convert XX to 20XX" but with the amount of cells that
I'm working with, this will take forever! Is there an easier way to do this?

 

[Pete_UK]

If you make it:

=1*LEFT(A2,8)

and you have valid date strings, then this will convert the string
into Excel date formats, so you can use Format | Cells | Number tab to
display the date with 4 digit years (eg mm/dd/yyyy in the Custom box).

Hope this helps.

Pete

 

[Roger Govier]

Hi

Try
=TEXT(LEFT(A1,8),"dd mm yyyy")