Excel cell format [$-10409]m-d-yyyy h:mm:ss AM/PM

Question

I have a c# application where data is read from an MS excel file. The excel cell format is [$-10409]m-d-yyyy h:mm:ss AM/PM

I am using ExcelDataReader from NuGet to read data from the excel file.

var file = new FileInfo(strFilePath);
        using (var stream = new FileStream(strFilePath, FileMode.Open))
        {
            IExcelDataReader reader = null;
            if (file.Extension == ".xls")
            {
                reader = ExcelReaderFactory.CreateBinaryReader(stream);

            }
            else if (file.Extension == ".xlsx")
            {
                reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }

            ds = reader.AsDataSet();
            dt = ds.Tables[0];
        }

When I read data, the value is changed. e.g. 7-1-2016 11:05:00 AM is converted to 42552.4618055556.

Is there any way to get the correct value while reading?

Thanks @Scroog1. It worked :) – Sobhan Jul 25 '16 at 14:58 — Sobhan, Jul 25 '16 at 14:58

score 0 · Answer 1 · edited Jul 25 '16 at 14:37

0

The date you see as a double is the number of days since 1900-01-01. If you take 42552.46/365=116.58.

Using this method to translate is not suggested as leap years do exist. You can reformat the cell by:

Highlight cells
Click format cells
Choose date
Choose date visualization format

edited Jul 25 '16 at 14:37

Kurt Van den Branden

11,995
10
76
85

answered Jul 25 '16 at 13:58

Mysterio

1
1

The excel file (which is input for my application) is generated from some other application (SSIS). I can't reformat it. – Sobhan Jul 25 '16 at 14:04

Excel cell format [$-10409]m-d-yyyy h:mm:ss AM/PM

1 Answers1