extract a date from an awkward string

[WhytheQ]

I have a load of strings that are all like this: "060513"
these strings are actually dates of format YYMMDD i.e the above is 13th
May 2006
is there a formula to convert to dates?

Regards
Jason

 

[Max]

I have a load of strings that are all like this: "060513"
these strings are actually dates of format YYMMDD i.e the above is 13th
May 2006
is there a formula to convert to dates?

Easier to just use Data > Text to Columns for this ..

Select the column of strings

Click Data > Text to Columns
Click Next > Next to proceed to step 3 of the wiz

In Step 3, Under Column data format,
check "Date", select: YMD from the droplist
Click OK

That should convert the entire col of strings to real dates

Now we can format the column in the desired date format
(via Format > Cells > Date)

 

[Max]

is there a formula to convert to dates?

Here's an example formula to convert the strings to real dates:
Assuming source strings in A1 down
Put in B1: =DATE(("20"&LEFT(A1,2)),MID(A1,3,2),RIGHT(A1,2))
Copy down
Then format col B in desired date format