I have a CSV file which contains dates formatted as m/dd/yyyy as follows
When I open the file in Excel, the dates are correctly converted - 1/06/2013 has numeric value 41,426.00 and the MONTH() function returns 6.
When I use Microsoft.Office.Interop.Excel to open the same file using either the Open() or OpenText() method the date gets converted to 41,280.00 (6/01/2013).
Can you open a CSV file using Microsoft.Office.Interop.Excel so that it opens the same as if the user opened the file from the windows shell?
Found the answer myself buried in the bottom of this question - Excel VBA date formats/values change when file is opened programatically
workbook = workbooks.Open(filename, Local: true)
Open() has a Local property, when false it assumes the mdy order for ambiguous dates, when true it uses the local machine setting which in Australia is dmy