text to dates?
 

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..?

whe
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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=401411

Suppose your text string is in A1, then use:

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


Mangesh




"AmyTaylor" wrote
in message ...

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..?

whe
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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=401411

On Fri, 2 Sep 2005 04:22:55 -0500, AmyTaylor
wrote:


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..?

whe
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

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

Regards,
KL


"AmyTaylor" wrote
in message ...

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..?

whe
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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=401411

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 wrote:

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

Regards,
KL

"AmyTaylor" wrote
in message ...

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..?

whe
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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=401411


--

Dave Peterson

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

Regards,
KL


"Dave Peterson" wrote in message
...
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 wrote:

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

Regards,
KL

"AmyTaylor"
wrote
in message ...

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..?

whe
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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread:
http://www.excelforum.com/showthread...hreadid=401411


--

Dave Peterson