Date conversion issue with Excel Interop and CSV file

I have a CSV file which contains dates formatted as m/dd/yyyy as follows

1/06/2013,15,1,1/06/2013 0:15,1,6.44

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?

Regards Dave

Answer1:

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

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Date conversion issue with Excel Interop and CSV file