Converting date format

[Maki]

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.

 

[Barb Reinhardt]

You may need to do "Text to Columns" and format as a date. I know it sounds
odd, but it works.

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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!).

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.