altering dates using regular expressions?

[Guest3731]

I have a column full of dates in some type of European date-format,
e.g.: 01/12/1974, where the actual date is December 1, 1974. Can I
use regular expressions or the like to change all of these to
12/01/1974?

Thank you.

 

[muddan madhu]

Go to control panel | Date, Time, Language, and Regional Options |
click on the change format
of number........ | Regional option | customize | date | change the
short date type : mm/dd/yyyy
click apply | ok | apply | ok

 

[Guest3731]

Go to control panel | Date, Time, Language, and Regional Options |
click on the change format
of number........ | Regional option | customize  | date | change the
short date type : mm/dd/yyyy
click apply | ok | apply | ok

Thank you for the reply, but I am looking only to change the actual
strings/text within a particular document (as if they were just
strings of characters, not dates). I believe what you have suggested
would affect the entire system.

 

[Dave Peterson]

If the values are dates, then you can reformat them to show the order (mdy) you
like.

But if the dates are really text, you can select the column.
Data|text to columns (xl2003 menus)
fixed width (but remove any lines that excel guesses)
choose date and dmy
And then finish up the wizard.

Then format the range the way you like.

If the values are a mixture of dates and text, then I'd reimport the raw data as
plain old text, and then use data|text to columns.

 

[Alan]

This might work

=LEFT(A1,2)&"-"&MID(A1,4,2)&"-"&RIGHT(A1,4)*1

and format the cell as you wish.

Regards,
Alan.

Go to control panel | Date, Time, Language, and Regional Options |
click on the change format
of number........ | Regional option | customize | date | change the
short date type : mm/dd/yyyy
click apply | ok | apply | ok

Thank you for the reply, but I am looking only to change the actual
strings/text within a particular document (as if they were just
strings of characters, not dates). I believe what you have suggested
would affect the entire system.

 

[Salmon Egg]

I have a column full of dates in some type of European date-format,
e.g.: 01/12/1974, where the actual date is December 1, 1974. Can I
use regular expressions or the like to change all of these to
12/01/1974?

Thank you.

This is an interesting problem. I am investigating it out of curiosity.
I might get back to it later.

I see two possible complications, and there could be more.

1. Excel likes to store a data as an integer. To display the date,

2. In an ordinary copy and paste, the format accompanies the value. Yo

Excel takes the integer, and extracts the day of the month, the month,
and the number of the year. With those values in hand, I believe that
excel displays the result as text. As someone else has suggested, you
can use the text functions to parse out these values. Then you can use
the DATE() function to store the integer.

Anyhow I put a date into a cell. I placed the integer 24103 into that
cell (A1). Then the next two cells on the right (B1 and C1) were
formatted formated using custom formatting with mm/dd/yyyy and
dd/mm/yyyy respectively. Then I copied A1 and pasted special into Ba and
C1. The these cells then displayed 12/28/1969 and 28/12/1969
respectively.

I noticed that it is difficult to remember to use Paste Special values.
I also noticed that when I clicked on C1 when copying it to this post,
its display went to 12/28/1969.

Bill