I was using Microsoft Excel to format a CSV (Comma Separated Values) file to display dates in the format YYYY-MM-DD. The dates were in DD/MM/YYYY format which is the one used in Canada. When I changed the date formating in Excel for the column to English (Canada) format it assumed that the dates were in mm/dd/yyyy format and made the following mistakes:

1. Dates where the day was > 12 (eg. 23/6/2010) it did not convert to date.
2. Dates where the day was < 12 (eg. 3/6/2010) it changed so the month was changed to day.

Microsoft support has addressed this problem – http://support.microsoft.com/kb/281125.

In my case the regional setting for my operating system was US with a default date format as mm/dd/yyyy. I had to change the regional settings to Canada – http://support.microsoft.com/kb/307938

So the solution for me was:

1. Go to Control Panel and select Regional Settings. Change the country to Canada and the date format there automatically changes to dd/mm/yyyy.
2. Open the csv manually and not from within Excel. This way it retains its data format.