Problem with dates

[Guest]

I have the following problem in Excel XP:

I need to automatically convert dates in this format: dd/mm/yy to mm/dd/yy.
Is there a function to do this?

This is because I import dates from Visual Basic thta come in the format
mm/dd/yy and as my Excel XP is in Spanish I have dd/mm/yy.

Thks in advance!

Regards.
Emece.-

 

[Frank Kabel]

Hi
if the dates are stored as real Excel dates you only have to change the
format ('format - Cells')

 

[Guest]

Thks Frank, but the dates are stored as text.

Hi
if the dates are stored as real Excel dates you only have to change the
format ('format - Cells')

 

[Frank Kabel]

Hi
try the following formula in a helper column
=DATE("20"&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))
and copy down

 

[Guest]

I tried it but look here is what I obtained:

A1: 12/20/2004

Applied the formula and obtained: 07/06/2004

Don't get it...

 

[Dan]

If your import is 11/22/04 in text, (mm/dd/yy), try to put
the formula =VALUE(D12) in the next cell and set the
format in what ever you need. You can customize the
format to your needs.

Go to Format Cell, Custom Category, and type the format
you need, whether it is mm/dd/yy, or dd/mm/yy.

I usually put it in the next column, inserting if need be.

If it works, copy it down for all the cells. Then copy
all the formulas and paste value over the cells to get rid
of the formulas.

 

[Frank Kabel]

Hi
then use:
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

 

[Gord Dibben]

Emece

Try Data>Text to Columns>Next>Next>Column Data Format>Date MDY or DMY
depending upon what you want.

Gord Dibben Excel MVP