19294

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

  • advanced filter excel with c#
  • convert .net 4.0 syntax OfType to .net 3.5
  • Saving file from JavaScript string without hitting server
  • C# Equivalent to this code
  • Can we Create Smart Device Project in VIsual Studio 2015 Professional
  • Java Generic Type Inference Strange Behavior?
  • mfc - MessageBox with rich text
  • How to get list of users who's birthday is today in MongoDB
  • How to use arithmetic operators with SAS macro variables [duplicate]
  • Open an application in a space using applescripts
  • Android custom URI scheme incorrectly encoded when type in browser
  • Synchronize windows folders
  • powershell Get-Counter -ComputerName parameter on Windows 7
  • Xamarin MonoAndroid Azure mobile service InsertAsync
  • HTML5 video only works in IE. The other browsers shows the black screen
  • Find Previous month name using Calender or any classes that in java
  • Private IP address in reserved subnet range
  • How To Customize ASP.NET Chart Databound To SqlDataSource
  • Invalid Date on validation Date of js
  • Looking for good analogy/examples for monitor verses semaphore
  • How to write order and limit within cakephp joins array
  • Is there any way to access browser form field suggestions from JavaScript?
  • Android screen density dpi vs ppi
  • Dynamically accessing properties of knockoutjs observable array
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • script to move all files from one location to another location
  • WinForms: two way TextBox problem
  • Fill an image in a square container while keeping aspect ratio
  • Can I make an Android app that runs a web view in Chrome 39?
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Turn off referential integrity in Derby? is it possible?
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Reading document lines to the user (python)
  • Binding checkboxes to object values in AngularJs
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?