1

Closed

Datetime is 1 day off

description

Greetings,

I'm trying to write a datetime to a cell using ClosedXML. I set the IXLCell DataType to be XLCellValues.DateTime, and the value to be a new DateTime. I also set the number format id to 14 (mm/dd/yyyy)

When I look at the excel sheet, the date displayed is always one day larger than anticipated. When I read the cell value using ClosedXML (in a unit test), the value read from the file matches the value I wrote, but the actual excel sheet does not show the correct value.

Is this a bug in ClosedXML or am I missing something?

Thanks!
-Adam
Closed Nov 11, 2016 at 9:59 AM by igitur

comments

jesta1215 wrote Aug 3, 2016 at 9:55 PM

I found the issue. https://msdn.microsoft.com/en-us/library/system.datetime.tooadate(v=vs.110).aspx

ToOADate stores dates as days since 30 december 1899, but excel stores them as days since 31 december 1899.

Therefore, we need to subtract 1 from the ToOADate value when writing to an excel file, and add 1 to the value when reading from the file.

igitur wrote Sep 27, 2016 at 10:51 AM

I'm using the latest codebase on the develop branch on Github and I can't replicate this problem.

Here is my code;
public void Create(String filePath)
{
    var workbook = new XLWorkbook();
    var worksheet = workbook.Worksheets.Add("Sample Sheet");
    worksheet.Cell("A1").Value = "Hello World!";
    var cell = worksheet.Cell("A2");
    cell.Value = new DateTime(2016, 1, 1);
    cell.DataType = XLCellValues.DateTime;
    cell.Style.NumberFormat.NumberFormatId = 14;
    workbook.SaveAs(filePath);
}
If I open the generated Excel file, I see "2016-01-01" in the cell.

Can you please post a simple example to reproduce your problem?

igitur wrote Oct 6, 2016 at 4:28 PM

I'd really love to know whether this is still an issue for you. Please let me know.