Converting data into datetime from excel

Question

User2023935253 posted

Hi,

From the excel sheet I read a cell data and converting it to datetime datatype. It reports specified cast is invalid error. Because, the cell data contains the value 43449. Thats why it caused the error. But when I convert this cell into date format in excel sheet then the value changed to 15-12-2018. Then I upload the excel sheet it ran successfully. My question is, how that integer value is related to this date. Is it possible to do the same logic in the code itself, coz the data is like that only for most of the excel documents.

Thanks

Answer 1

User475983607 posted

You are confusing a Date type with a string formatted as a Date. 

I recommend using OpenXml for handing Excel in ASP.NET.

https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet

Answer 2

User753101303 posted

Hi,

In Excel dates are stored as numbers. You can use  https://docs.microsoft.com/en-us/dotnet/api/system.datetime.fromoadate?view=netframework-4.7.2 to convert this kind of number to an actual date the same way than Excel does (if I remember Excel does have though a setting to reproduce a bug on earlier products with year 1900).

Answer 3

User-1716253493 posted

Dim i As Integer = 43449
Dim dt As Date = Date.FromOADate(i)