text to dates?

[AmyTaylor]

Hi everyone, I have a problem with this following query:

I have a list of cells in the text format: 01.01.05 which relates to
1st Jan 2005 is there a way to change this so that it becomes a
date..?

where:
01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).

Thank you for all your help.
love Amy xx

 

[Mangesh Yadav]

Suppose your text string is in A1, then use:

=DATE(YEAR(RIGHT(A1,2)),MONTH(MID(A1,4,2)),DAY(LEFT(A1,2)))


Mangesh

 

[Ron Rosenfeld]

Hi everyone, I have a problem with this following query:

I have a list of cells in the text format: 01.01.05 which relates to
1st Jan 2005 is there a way to change this so that it becomes a
date..?

where:
01.01.05 (text) would become 01/01/2005 (dd/mm/yyyy date format)
05.10.05 (text) would become 05/10/2005 (dd/mm/yyyy date format).

Thank you for all your help.
love Amy xx

If your dates are in columns:

Select the dates.

Data/Text-to-Columns Fixed Width (i.e. don't separate them)
Next
Next
Column Data Format/Date: DMY
Finish


--ron

 

[KL]

how about: =--SUBSTITUTE(A1,".","/") and format the cell as date.

Regards,
KL

 

[Dave Peterson]

With my windows short date set for mm/dd/yyyy, I got May 10, 2005.

But I think (untested) if the short date format matched (dmy), then the formula
would work fine.

 

[KL]

Yup, that was exactly the idea as the OP appears to be using the European
notation.

Regards,
KL