Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Text Entry to Date Format | Excel Discussion (Misc queries) | |||
how do I convert an excel spreadsheet to cif format? | Excel Discussion (Misc queries) | |||
Convert hours and minutes in time format into fractions of hours.. | Excel Worksheet Functions | |||
CONVERT 11/23/04 US DATE FORMAT TO EUROPEAN 23/11/04 FATE | Excel Discussion (Misc queries) | |||
Any way to force European format dates in sheets? | New Users to Excel |