The correct date cannot be read

[zhuangjiaju]

Describe the bug

I expected the date to be: 2022-06-17 23:59:59, but instead I got 2022-06-17 23:59:58

Screenshots

Steps to reproduce the behavior:

// Open the document as read-only.
using (SpreadsheetDocument spreadsheetDocument =
       SpreadsheetDocument.Open("/Users/zhuangjiaju/Downloads/date2.xlsx", false))
{
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
    string text;
    foreach (Row r in sheetData.Elements<Row>())
    {
        foreach (Cell c in r.Elements<Cell>())
        {
            text = c.CellValue.Text;
            double doubleTime = double.Parse(c.CellValue.InnerXml);
            DateTime dateTime = DateTime.FromOADate(doubleTime);
            Console.WriteLine(dateTime.ToString("yyyy-MM-dd HH:mm:ss") + " ");
        }
    }
}

Observed behavior A clear and concise description of what you expected to happen. date2.xlsx

Expected behavior A clear and concise description of what you expected to happen.

Desktop (please complete the following information):

• OS: [e.g. Windows]
• Office version [e.g. 16.0.15427.20178]
• .NET Target: (e.g. .NET Framework, .NET Core, UWP, Xamarin...)
• DocumentFormat.OpenXml Version: (e.g. 2.11.0)

Additional context Add any other context about the problem here.

[tomjebo]

@zhuangjiaju If you add the more precise format specifier, you'll see that the underlying double value has more precision and hence the reason for Excel's rounding to 23:59:59 on the last cell. If you use this code:

Console.WriteLine(dateTime.ToString("yyyy-MM-dd HH:mm:ss.ff") + " ");

you'll see this:

2022-06-15 23:59:59.00
2022-06-16 23:59:59.00
2022-06-17 23:59:58.99

If you need rounding to nearest seconds there is a StackOverflow discussion for System.DateTime here: https://stackoverflow.com/questions/21704604/have-datetime-now-return-to-the-nearest-second

Hope this helps.