Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,118
Default CONVERT DATES FROM EUROPEAN FORMAT TO US 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


"les8" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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,


"Ron Coderre" wrote:

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


"les8" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,118
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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


"les8" wrote:

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,


"Ron Coderre" wrote:

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


"les8" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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

les8

"Ron Coderre" wrote:

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


"les8" wrote:

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,


"Ron Coderre" wrote:

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


"les8" wrote:

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.



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,440
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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. EditCopy. Select your dates. EditPaste Special, check Multiply. Format as
date.

Backup your workbook before you try!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"les8" wrote in message ...
|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.


  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,118
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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


"les8" wrote:

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

les8

"Ron Coderre" wrote:

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


"les8" wrote:

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,


"Ron Coderre" wrote:

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


"les8" wrote:

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.

  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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
"Ron Coderre" wrote:

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


"les8" wrote:

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

les8

"Ron Coderre" wrote:

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


"les8" wrote:

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,


"Ron Coderre" wrote:

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


"les8" wrote:

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.

  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,118
Default CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT

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


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

XL2002, WinXP


"les8" wrote:

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
"Ron Coderre" wrote:

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


"les8" wrote:

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

les8

"Ron Coderre" wrote:

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


"les8" wrote:

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,


"Ron Coderre" wrote:

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


"les8" wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Text Entry to Date Format User Excel Discussion (Misc queries) 1 November 16th 05 09:40 PM
how do I convert an excel spreadsheet to cif format? Karen Excel Discussion (Misc queries) 1 May 16th 05 06:26 PM
Convert hours and minutes in time format into fractions of hours.. Akern Excel Worksheet Functions 4 April 21st 05 02:56 PM
CONVERT 11/23/04 US DATE FORMAT TO EUROPEAN 23/11/04 FATE Roland Excel Discussion (Misc queries) 2 December 20th 04 11:19 PM
Any way to force European format dates in sheets? neil f New Users to Excel 6 December 12th 04 10:52 PM


All times are GMT +1. The time now is 08:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"