Date format string exception

[hershaw]

EPPlus usage

Commercial use (I have a commercial license)

Environment

OSX

Epplus version

8.4.0

Spreadsheet application

excel

Description

If I open a date formatted like this in excel, it renders. but in EPPlus it throws an error.

public void EPPlus_ThrowsFormatException_ForMalformedDateFormat()
    {
        using var pkg = new ExcelPackage();
        var ws = pkg.Workbook.Worksheets.Add("Test");

        ws.Cells["A1"].Value = new DateTime(2024, 12, 31);

        ws.Cells["A1"].Style.Numberformat.Format = "[$-en-US]mmm d, 'yy;@";

        // Directly accessing cell.Text should throw FormatException
        // This documents the bug that our workaround addresses
        var ex = Assert.Throws<FormatException>(() => ws.Cells["A1"].Text);
        Assert.Contains("quote", ex.Message, StringComparison.OrdinalIgnoreCase);
    }

[AdrianEPPlus]

We will take a look at this

[AdrianEPPlus]

So what happens is that we call .ToString() on the date with the format you have specified. .ToString() uses .NETs format syntax and having a single ' is not a valid syntax. You can slove this by wither escaping the character or use double quotes,.

ws.Cells["A1"].Style.Numberformat.Format = "[$-en-US]mmm d, ''yy;@";
or
ws.Cells["A1"].Style.Numberformat.Format = "[$-en-US]mmm d, \\'yy;@";

Update: After checking some more, there are slight differences what Excel shows in its GUI and what is actually written to the xml. If you want to make sure you have the correct format you can extract it from the xml by following this guide on our wiki:

https://github.com/EPPlusSoftware/EPPlus/wiki/Formatting-and-styling#number-formats

Since I have already checked this is what your format code is saved as in the XML: [$-409]mmm\ d\,\ \'yy;@

and to assign it as a format in EPPLus it would look like this since we have to escape all \ characters: "[$-409]mmm\\ d\\,\\ \\'yy;@";