Excel swaps month & day in csv import

Excel swaps month & day in csv import

Didn't find your answer?

When I export in csv file from either Sage or TAS, Excel swaps the month and day for any day up to 12 eg 03/04/2012 becomes 04/03/2012 but 15/04/2012 will not change.

Does anyone know how to avoid this or, failing that, the easiest way to correct it?

Thanks

Replies (10)

Please login or register to join the discussion.

avatar
By paulwakefield1
18th Feb 2013 15:17

It sounds as if Excel is

trying to use a US date format but gives up when it gets an impossible date (e.g. there is no month 15).

Check the date format you are using in the Excel sheet. Failing that, you may need to change your Windows national settings.

 

Thanks (0)
avatar
By Alf
18th Feb 2013 15:24

Unfortunately that's not the answer

Thanks - I understand what Excel is trying to do but not why or how to stop it.

Having Googled it, this is a common problem but I haven't found a solution - both Excel and Windows settings are already set to UK and dd/mm/yyyy.

Thanks (0)
avatar
By dnicholson
18th Feb 2013 15:52

Cell format

If you highlight the cell and choose Format Cell, then Date, there's a Locale choice below the date formats. Try setting a specific locale. It sounds like it's defaulting to US date except when it can't. Of course the CSV file has no format information, so you may need to repeat this.

Thanks (0)
avatar
By Alf
18th Feb 2013 16:01

Thanks - the Locale is already set to UK

Thanks (0)
avatar
By paulwakefield1
18th Feb 2013 16:48

Can you confirm

that, under Region & Language, both the Formats tab AND the Date tab under the Additional settings button are set to UK formats (Windows 7)?

I have seen this problem before and I am trying to remember how it was solved - the tabs under additional settings are ringing a loud bell!

Thanks (0)
avatar
By chetan
18th Feb 2013 16:48

The following method works in Excel 2007 and I guess the process will be similar for other versions:

 

Open a new blank workbook.In the "Data" menu ribbon, look for the "Get External Data" group and click on the "From Text" button.Browse to the csv file that you want to open and click on "Import".  This will bring up a 3-step data import wizard. The second step of the wizard allows you to specify which character is used to delimit the fields.The third step allows you to specify what is in each field (i.e. General, Text, Date or Do Not Import).  For date fields you can specify the format (i.e. DMY, YMD, MYD, DYM, YDM).

Thanks (0)
avatar
By GW
18th Feb 2013 16:52

Date and text format

Excel records dates as numbers - assuming 1900 was a leap year and counting 1st January 1900 as day 1 

I don't know about TAS but going from Sage excel gets confused between dates and text interpreting 03/04/2012 as text. Try this:

enter the number 1 in a blank cell

Copy the 1 then paste special - multiply, to the cells with the dates, this should convert the cell contents to numbers then apply the appropriate date format to the cells.

Thanks (1)
avatar
By Alf
18th Feb 2013 17:16

Now working!

@ Paul - Thanks - there was 1 setting set to US but it was just the language for non-Unicode programs (whatever that is). I have changed it to UK and will see if that makes a difference after I restart the computer

@chetan - Thanks - that seems to do the trick (I am using Excel 2003) but by using Data/Import External Data, it seems to bring it through properly. Much apppreciated - I can't tell you how many hours I have spent on this over the last year or so.

Thanks (0)
avatar
By Alf
18th Feb 2013 17:16

Paste Special doesn't help

Thanks GW - I had tried that - it didn't work

Thanks (0)
avatar
By mikeopolo
01st Mar 2013 04:20

Try using a short date format of yyyy/MM/dd

Nice idea about getting external data.

If you change the short date settings to the above, then create the csv, it "may" pick up this setting. If so, Excel should read all the dates correctly.

The capital MM (in yyyy/MM/dd) is important.

Then change back afterwards.

If this works I can post a VBS script to automate the change in the short date setting.

Thanks (0)