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