US->UK date change

**squickybiggy** · 12-04-2006, 07:12 AM

Hi,

I was wondering if anyone knew how I might change a load of dates that I have in US format to UK format. The dates were orginially saved as
mm-dd-yyyy, but excel has "recognised" some where the dates are before the 13th of the month and changed them to dd/mm/yyyy as my regional settings are set to the UK. So, for example, the second of May 2006 would originally be 04-02-2006, but excel changes it to 04/02/2006, or the fourth of February 2006.

It also will not recognise the dates that are over the 12th of the month, so if I change my regional settings to US, they still stay as a block of text, eg 04-15-2006 for the 15th April does not get recognised.

Basically I wondered if there was a macro I could use to switch the mm and dd parts around for all the dates, so that they are recognised in the UK format.

I hope I haven't made this sound more complicated than it already is.

Thanks very much.

**Bryan Hessey** · 12-04-2006, 07:15 AM

Originally Posted by squickybiggy

Hi,

I was wondering if anyone knew how I might change a load of dates that I have in US format to UK format. The dates were orginially saved as
mm-dd-yyyy, but excel has "recognised" some where the dates are before the 13th of the month and changed them to dd/mm/yyyy as my regional settings are set to the UK. So, for example, the second of May 2006 would originally be 04-02-2006, but excel changes it to 04/02/2006, or the fourth of February 2006.

It also will not recognise the dates that are over the 12th of the month, so if I change my regional settings to US, they still stay as a block of text, eg 04-15-2006 for the 15th April does not get recognised.

Basically I wondered if there was a macro I could use to switch the mm and dd parts around for all the dates, so that they are recognised in the UK format.

I hope I haven't made this sound more complicated than it already is.

Thanks very much.

I presume these were from a .csv file - do you still have the file?

**squickybiggy** · 12-04-2006, 07:22 AM

Yes, it's all in a .csv file. I changed the extension to .doc and attached it if you want to have a look.

**Bryan Hessey** · 12-04-2006, 07:24 AM

Originally Posted by squickybiggy

Yes, it's all in a .csv file. I changed the extension to .doc and attached it if you want to have a look.

no

change the ext to .txt and import again, and in the Wizard set the date to DMY or MDY for the field as required.

hth
---

**squickybiggy** · 12-04-2006, 07:28 AM

Ok, saved it as a .txt file, but I don't follow about the wizard. I tried opening it in excel again and it just opened as before. I have attached the .txt file.

**Bryan Hessey** · 12-04-2006, 07:33 AM

Originally Posted by squickybiggy

Ok, saved it as a .txt file, but I don't follow about the wizard. I tried opening it in excel again and it just opened as before. I have attached the .txt file.

in Excel, File, Open (.txt) it should go to the Import wizard, third screen of which allows the setting of a date format. Tick and set to MDY

---