Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Converting date format
A downloaded dataset has dates in American format, i.e. mm/dd/yyyy, whereas
my regional setting is dd/mm/yyyy. It was downloaded in csv so currently the cells, those dates are in, are formatted "General". What's the easiest way to convert all mm/dd/yyyy dates to dd/mm/yyyy? I thought this would be straight forward but couldn't find example in Help file. I'm using Excel 2007. Thank you. -- Maki @ Canberra.AU |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Converting date format
You may need to do "Text to Columns" and format as a date. I know it sounds
odd, but it works. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Maki" wrote: A downloaded dataset has dates in American format, i.e. mm/dd/yyyy, whereas my regional setting is dd/mm/yyyy. It was downloaded in csv so currently the cells, those dates are in, are formatted "General". What's the easiest way to convert all mm/dd/yyyy dates to dd/mm/yyyy? I thought this would be straight forward but couldn't find example in Help file. I'm using Excel 2007. Thank you. -- Maki @ Canberra.AU |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Converting date format
The bad news is that by the time you open the file in excel, some of the data
will be treated as text and some of the data will be treated as a date--but it's the wrong date. I'd rename the .csv file to .txt and open the text file. Then I'd see the text to columns wizard and I could choose date and mdy in that wizard. After the data is brought in as the correct dates, I can reformat the cells to display whatever I like. Maki wrote: A downloaded dataset has dates in American format, i.e. mm/dd/yyyy, whereas my regional setting is dd/mm/yyyy. It was downloaded in csv so currently the cells, those dates are in, are formatted "General". What's the easiest way to convert all mm/dd/yyyy dates to dd/mm/yyyy? I thought this would be straight forward but couldn't find example in Help file. I'm using Excel 2007. Thank you. -- Maki @ Canberra.AU -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Converting date format
Thank you, Dave and Barb. Yes, this did work.
But what if the downloaded dataset was in xls rather than csv or txt and had dates in non-DMY format? Would it be good to first export in csv format and then import it back in, using the text to column wizard? Thanks again. -- Maki @ Canberra.AU "Dave Peterson" wrote: The bad news is that by the time you open the file in excel, some of the data will be treated as text and some of the data will be treated as a date--but it's the wrong date. I'd rename the .csv file to .txt and open the text file. Then I'd see the text to columns wizard and I could choose date and mdy in that wizard. After the data is brought in as the correct dates, I can reformat the cells to display whatever I like. Maki wrote: A downloaded dataset has dates in American format, i.e. mm/dd/yyyy, whereas my regional setting is dd/mm/yyyy. It was downloaded in csv so currently the cells, those dates are in, are formatted "General". What's the easiest way to convert all mm/dd/yyyy dates to dd/mm/yyyy? I thought this would be straight forward but couldn't find example in Help file. I'm using Excel 2007. Thank you. -- Maki @ Canberra.AU -- Dave Peterson |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Converting date format
If the values in the cell are really dates, then you may find that things are
perfect as they are. Depending on the date format that the sender used, you may find that when you open the file, you see the dates the way you like. But if the sender didn't use a date format that excel will treat nicely, then you should be able to just change the numberformat to what you want. You can verify the dates by changing to a non-ambiguous date format--then change to what you like (and I hope it's non-ambiguous!). Maki wrote: Thank you, Dave and Barb. Yes, this did work. But what if the downloaded dataset was in xls rather than csv or txt and had dates in non-DMY format? Would it be good to first export in csv format and then import it back in, using the text to column wizard? Thanks again. -- Maki @ Canberra.AU "Dave Peterson" wrote: The bad news is that by the time you open the file in excel, some of the data will be treated as text and some of the data will be treated as a date--but it's the wrong date. I'd rename the .csv file to .txt and open the text file. Then I'd see the text to columns wizard and I could choose date and mdy in that wizard. After the data is brought in as the correct dates, I can reformat the cells to display whatever I like. Maki wrote: A downloaded dataset has dates in American format, i.e. mm/dd/yyyy, whereas my regional setting is dd/mm/yyyy. It was downloaded in csv so currently the cells, those dates are in, are formatted "General". What's the easiest way to convert all mm/dd/yyyy dates to dd/mm/yyyy? I thought this would be straight forward but couldn't find example in Help file. I'm using Excel 2007. Thank you. -- Maki @ Canberra.AU -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting to regular date format | Excel Discussion (Misc queries) | |||
Converting to Date Format | Excel Discussion (Misc queries) | |||
Converting US Date Format | Excel Worksheet Functions | |||
Converting date format | Excel Discussion (Misc queries) | |||
Converting to date format | Excel Discussion (Misc queries) |