How to fix incorrect date and time values from Excel?

Question

I am using EPPlus excelpackage with c# to get values from a spreadsheet. The spreadsheet has two separate columns Date and Time. The issue is the date value is 2/4/2020 but it returns as 2/4/2020 12:00:00 AM. The time value is 12:21 AM but it returns 12/30/1899 12:21:00 PM. My desired result is to return the values as they are in the spreadsheet. I am not clear on why this is happening or how to fix the issue for my needs.

using (ExcelPackage package = new ExcelPackage(fs))
    {
        ExcelWorkbook excelWorkBook = package.Workbook;
        ExcelWorksheet ws = excelWorkBook.Worksheets.First();
        int rowCount = 0;
        rowCount = ws.Dimension.End.Row;

        for (int r = 2; r <= rowCount; r++)
        {
            string status = ws.Cells[r, 11].Value?.ToString();
            string deliveredDate = ws.Cells[r, 15].Value?.ToString();
            string deliveredTime = ws.Cells[r, 17].Value?.ToString();
        }
        fs.Close();
    }
}

THere's nothing wrong. Excel stores dates as OADate values - essentially doubles with a specific meaning. Epplus understands OADates and will store `DateTime` values as OADates. How dates or times appear depends on the cell's *style*. If you modify the string, you break the date. *Don't* touch the value, modify the cell style instead — Panagiotis Kanavos, Feb 07 '20 at 17:14
I am not clear on your suggestion. If you mean modify the cell style in the spreadsheet, I don't have permissions to do that. Should I parse out the values I need? — Roy Rogers, Feb 07 '20 at 17:18
Use eg `ws.Cells[r, 11].Style.Numberformat.Format = "yyyy-mm-dd";` or `ws.Cells[r, 11].Style.Numberformat.Format = "mm/dd/yyyy";` to display the value as a date only. `hh:mm` for time, etc — Panagiotis Kanavos, Feb 07 '20 at 17:19
You're already trying to modify the spreadsheet. The problem is the *wrong* style. You have to fix it. There's nothing wrong with the value, the problem is the *style*. — Panagiotis Kanavos, Feb 07 '20 at 17:20
The [DateTime.ToOADate](https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?redirectedfrom=MSDN&view=netframework-4.8#System_DateTime_ToOADate) docs explain what an OADate is, end explains that the base is actually Dec 30, 1899. As a result, if a time value is formatted as a *date*, it will appear as `12/30/1899 12:21:00 PM` — Panagiotis Kanavos, Feb 07 '20 at 17:23
Explanations by Panagiotis Kanavos and Christopher were very helpful. Applying your example solved the issue. — Roy Rogers, Feb 07 '20 at 17:30

Christopher · Answer 1 · 2020-02-07T17:30:46.673

This is a normal mixup between the actuall value and 2 string representations of said value. Thise happen especially with DateTime values.

The issue is the date value is "2/4/2020" but it returns as "2/4/2020 12:00:00 AM"

Dates on their own do not really work. For decades now, we always have DateTimes. Date and Time as seperate types? Largely dropped. It is one of those things that are hard to explain, but become evident once you run into the underlying issue.

As no specific Time was given during input a default was asumed - 00:00:00 or 12 AM. Then the cell was set up to only display the "Date" part. Something equating to "2/4/2020 12:00:00 AM" is really what is there. The Display Engine called Microsoft Excel (or whatever you use) simply was told not to show that part via the Cell Formating.

How to fix incorrect date and time values from Excel?

1 Answers1