Text property of date cell formatted as mm/dd/yyyy is read incorrectly

[allenking1]

How to reproduce: Create an xlsx file. Format a cell as Date in mm/dd/yyyy format and enter any date value. Suppose row =2, column =3. Cell value 9/13/2018.

EPPlus code:

Dim entrydate As String = worksheet.Cells(2, 3).Text
entrydate is '13' instead of '9/13/2018'

If cell date format is changed to yyyy-mm-dd, the read is fine. It appears "/" and EPPlus has issues in cell texts.

[andreluizsecco]

Same problem here!
The workaround is to use this package: https://www.nuget.org/packages/EPPlus.Core/

[albertmata]

Same problem here, after updating from 4.1.1 to 4.5.3. Downgrading again....

[allenking1]

Yes this is an issue.  However, easy workaround is to set the date format on the cell to yyyy-mm-dd and then read the cell. It works for me.

…

On 2/21/2019 3:44 AM, Albert mata wrote: Same problem here, after updating from 4.1.1 to 4.5.3. Downgrading again.... — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#333 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/Ag-hSd0tIXLhm4YXib9ufTpZ-fTyri1Pks5vPoaagaJpZM4XcV5j>.

[telefrancisco]

Same here but with dd/mm/yyyy format. Tested with 4.5.3.1.

[dbloch3643]

Guessing this may have been introduced when fixing #196

[JanKallman]

I can't reproduce this in 4.5.3.1.

            using (var package = new ExcelPackage())
            {
                var ws = package.Workbook.Worksheets.Add("TextBug");
                ws.Cells["A1"].Value = new DateTime(2019, 3, 7);
                ws.Cells["A1"].Style.Numberformat.Format = "mm/dd/yyyy";

                Assert.AreEqual("03/07/2019", ws.Cells["A1"].Text);
            }

Can you provide some code to reproduce this bug.
Which Operating system, Regional Settings etc

[drabell]

Hi Jan,

Reported bug (i.e. incorrect Text property of date cell) seems to be specific to the Short Date format applied to Excel cells containing date value. In order to reproduce it:

1. Create Excel file (.xlsx) and enter sample date value (e.g. 3/14/2001) into the cell "A1"
2. Select aforementioned cell, click on Number Format dropdown menu and select "Short Date" list item
3. Save the file, open it with EPPlus package and read Text property of said cell: you will get erroneous valuelike "14" instead of entered 3/14/2001.
4. Looking further into this issue: open the Number dialog box and see the sample Date format corresponding to Short Date shown as "3/14/2001". The note below stated: "...Date formats that begins with an asterisk () respond to changes in regional date and time settings that are specified for the operating system". At the same time, the Style.Numberformat.Format cell property in said EPPLus test case appeared as "mm-dd-yy"

Solution
Quick workaround was found by applying different date format to said cell originally formatted w/Short Date, like in the code snippet shown below:

if (String.Equals (ws.Cells["A1"].Style.Numberformat.Format, "mm-dd-yy", StringComparison.InvariantCultureIgnoreCase)) { ws.Cells["A1"].Style.Numberformat.Format = "mm/dd/yyyy"; }

After applying the aforementioned patch, the EPPlus package returns the correct Text property value of said cell, i.e. "03/14/2001".

Note: the solution works in the OS/Excel settings pertinent to the US localization; it is not guaranteed to be universally applicable to other locales.

Best rgds,
Alex

PS. It's relevant to mention that in order to strictly conform to the Excel date format specs, the month should be entered as "MM" instead of "mm" (the latter is usually interpreted as minutes).

[JanKallman]

Thanks, found the problem and added a fix. Please give it a try

[jburley01]

@JanKallman I just upgraded to version 4.5.3.1 and it is still happening for me.

The cell format is a Date with the type '*3/14/2012' selected in the spreadsheet. When I put a date of '3/25/2019' in the cell, the cell.Text property is '25' instead of the full date. If I put an single quote in front of the date in the excel spreadsheet it pulls in the correct value in the cell.Text property.

Hope this helps figure out the issue.

[nbsoftware]

Thanks, found the problem and added a fix. Please give it a try

I don't think this commit is in the 4.5.3.1 release.
@JanKallman do you have a beta version that we can test against?

Or else, when do you plan to release this fix?

[belall-shaikh]

Hi, the issue still persists.