CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

[Guest]

I have a list of invoice dates where the day is first and the month is
second, and the year is third. I need to convert to the US format of month
first, day second and year third. Any suggestions are most appreciated.
Thanks.

 

[Guest]

Can you describe the values a little more?

Are they actual dates, that Excel displays, but the months and days are
transposed?
Where Dec 31 does not display as a date:
31/12/2006 would be a text entry, but
Dec 3rd would be 03/12/2006 and display as March 3rd.

Are they in some numeric or text format:
31122006 ?
31/12/2006 ?

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron,

The date shows as 22/4/05 (this is what is in the cell for April 22),
I need tis to be 4/22/05 or 04/22/2005 or 4/22/2005 - anything where the
month is first.
There appears to be no consistency in the format.
Some months are one digit and some are 2,
Some years are two digits and some are 4,
The days are either one or two digits.

8/7/2005
29/07/05
5/8/2005
22/04/05
21/10/05
3/11/2005
4/11/2005
22/11/05
22/12/05
Thanks,
les8

Ron,

the dates are in date format,

 

[Guest]

Try this:

Select the range of "dates"

From the Excel main menu:
<data><text to column>
Check: Fixed width..........Click the [next] button
Click the [next] button
Check: Date (in the upper right and select DMY from the dropdown list
Click the [Finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron,
Nothing changed. I think the dates are values, not text.

les8

Try this:

Select the range of "dates"

From the Excel main menu:
<data><text to column>
Check: Fixed width..........Click the [next] button
Click the [next] button
Check: Date (in the upper right and select DMY from the dropdown list
Click the [Finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Ron,

The date shows as 22/4/05 (this is what is in the cell for April 22),
I need tis to be 4/22/05 or 04/22/2005 or 4/22/2005 - anything where the
month is first.
There appears to be no consistency in the format.
Some months are one digit and some are 2,
Some years are two digits and some are 4,
The days are either one or two digits.

8/7/2005
29/07/05
5/8/2005
22/04/05
21/10/05
3/11/2005
4/11/2005
22/11/05
22/12/05
Thanks,
les8

Ron,

the dates are in date format,

 

[Niek Otten]

Are the cells all formatted the same pattern?
Did you try to just format them as mm/dd/yyyy?
One way to try and convert them to real Excel dates:

Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your dates. Edit>Paste Special, check Multiply. Format as
date.

Backup your workbook before you try!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a list of invoice dates where the day is first and the month is
| second, and the year is third. I need to convert to the US format of month
| first, day second and year third. Any suggestions are most appreciated.
| Thanks.

 

[Guest]

That technique worked for me, regardless of whether the values were numeric
or text.

Try this experiment:

Copy the dates from your 2nd posting into a new worksheet
Follow the same Text-to-Columns instructions
If that works....check if the copied data is different from your original data

Let us know what you find.
***********
Regards,
Ron

XL2002, WinXP

Ron,
Nothing changed. I think the dates are values, not text.

les8

Try this:

Select the range of "dates"

From the Excel main menu:
<data><text to column>
Check: Fixed width..........Click the [next] button
Click the [next] button
Check: Date (in the upper right and select DMY from the dropdown list
Click the [Finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Ron,

The date shows as 22/4/05 (this is what is in the cell for April 22),
I need tis to be 4/22/05 or 04/22/2005 or 4/22/2005 - anything where the
month is first.
There appears to be no consistency in the format.
Some months are one digit and some are 2,
Some years are two digits and some are 4,
The days are either one or two digits.

8/7/2005
29/07/05
5/8/2005
22/04/05
21/10/05
3/11/2005
4/11/2005
22/11/05
22/12/05
Thanks,
les8

Ron,

the dates are in date format,


:

Can you describe the values a little more?

Are they actual dates, that Excel displays, but the months and days are
transposed?
Where Dec 31 does not display as a date:
31/12/2006 would be a text entry, but
Dec 3rd would be 03/12/2006 and display as March 3rd.

Are they in some numeric or text format:
31122006 ?
31/12/2006 ?

***********
Regards,
Ron

XL2002, WinXP


:

I have a list of invoice dates where the day is first and the month is
second, and the year is third. I need to convert to the US format of month
first, day second and year third. Any suggestions are most appreciated.
Thanks.

 

[Guest]

Ron and Niek
I finally got it to work using both of your solutions. I copied the dates (
some were text and some values) did the paste-special multiply
which changed them all to values, then did the text to columns
suggestion which flipped the month and day.

Don't know why it worked, but I am back in business. Thanks
so much to both of you.

les8

That technique worked for me, regardless of whether the values were numeric
or text.

Try this experiment:

Copy the dates from your 2nd posting into a new worksheet
Follow the same Text-to-Columns instructions
If that works....check if the copied data is different from your original data

Let us know what you find.
***********
Regards,
Ron

XL2002, WinXP

Ron,
Nothing changed. I think the dates are values, not text.

les8

Try this:

Select the range of "dates"

From the Excel main menu:
<data><text to column>
Check: Fixed width..........Click the [next] button
Click the [next] button
Check: Date (in the upper right and select DMY from the dropdown list
Click the [Finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Ron,

The date shows as 22/4/05 (this is what is in the cell for April 22),
I need tis to be 4/22/05 or 04/22/2005 or 4/22/2005 - anything where the
month is first.
There appears to be no consistency in the format.
Some months are one digit and some are 2,
Some years are two digits and some are 4,
The days are either one or two digits.

8/7/2005
29/07/05
5/8/2005
22/04/05
21/10/05
3/11/2005
4/11/2005
22/11/05
22/12/05
Thanks,
les8

Ron,

the dates are in date format,


:

Can you describe the values a little more?

Are they actual dates, that Excel displays, but the months and days are
transposed?
Where Dec 31 does not display as a date:
31/12/2006 would be a text entry, but
Dec 3rd would be 03/12/2006 and display as March 3rd.

Are they in some numeric or text format:
31122006 ?
31/12/2006 ?

***********
Regards,
Ron

XL2002, WinXP


:

I have a list of invoice dates where the day is first and the month is
second, and the year is third. I need to convert to the US format of month
first, day second and year third. Any suggestions are most appreciated.
Thanks.

 

[Guest]

Glad to hear you resolved your issue.
Thanks for the update.


***********
Regards,
Ron

XL2002, WinXP

Ron and Niek
I finally got it to work using both of your solutions. I copied the dates (
some were text and some values) did the paste-special multiply
which changed them all to values, then did the text to columns
suggestion which flipped the month and day.

Don't know why it worked, but I am back in business. Thanks
so much to both of you.

les8

That technique worked for me, regardless of whether the values were numeric
or text.

Try this experiment:

Copy the dates from your 2nd posting into a new worksheet
Follow the same Text-to-Columns instructions
If that works....check if the copied data is different from your original data

Let us know what you find.
***********
Regards,
Ron

XL2002, WinXP

Ron,
Nothing changed. I think the dates are values, not text.

les8

:

Try this:

Select the range of "dates"

From the Excel main menu:
<data><text to column>
Check: Fixed width..........Click the [next] button
Click the [next] button
Check: Date (in the upper right and select DMY from the dropdown list
Click the [Finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Ron,

The date shows as 22/4/05 (this is what is in the cell for April 22),
I need tis to be 4/22/05 or 04/22/2005 or 4/22/2005 - anything where the
month is first.
There appears to be no consistency in the format.
Some months are one digit and some are 2,
Some years are two digits and some are 4,
The days are either one or two digits.

8/7/2005
29/07/05
5/8/2005
22/04/05
21/10/05
3/11/2005
4/11/2005
22/11/05
22/12/05
Thanks,
les8

Ron,

the dates are in date format,


:

Can you describe the values a little more?

Are they actual dates, that Excel displays, but the months and days are
transposed?
Where Dec 31 does not display as a date:
31/12/2006 would be a text entry, but
Dec 3rd would be 03/12/2006 and display as March 3rd.

Are they in some numeric or text format:
31122006 ?
31/12/2006 ?

***********
Regards,
Ron

XL2002, WinXP


:

I have a list of invoice dates where the day is first and the month is
second, and the year is third. I need to convert to the US format of month
first, day second and year third. Any suggestions are most appreciated.
Thanks.