Is there any way to format date cell in Excel with Invariant culture like in C#

Question

The problem I am facing is that in a cell of an Excel sheet, a date is written. The cell is formatted with custom-format like:

[$-de]dd/mm/yyyy (dddd)

But only the (dddd) part is processed to give me "Sonntag" (Sunday in German language).

The datetime format is not fixed even though I have written the culture as "de" for the custom format. Is there any way to achieve the following result without altering the datetime format(yyyy-mm-dd)?

Tried                       Output                   Expected output
--------                  -----------------------   -------------------
[$-fr]yyyy-mm-dd (dddd) => 2019-07-31 (dimanche)    31/7/2019 (dimanche)  
[$-de]yyyy-mm-dd (dddd) => 2019-07-31 (Sonntag)     31.7.2019 (Sonntag)
[$-en]yyyy-mm-dd (dddd) => 2019-07-31 (Sunday)      7.31.2019 (Sunday)

last tried code sample should have [$-en] as the language format. Typo :D — Gouse, Jul 11 '19 at 09:06
I edited the typo for you. You can use the [edit] link under your post to update it next time. — Gino Mempin, Jul 11 '19 at 09:22
Thanks Gino. I am novice in stack overflow. Your help is a warm welcome to the platform. :) I guess i will be around for a while ;) — Gouse, Jul 11 '19 at 09:37
In a similar way, If there is way to define formatting of a number, that would be a great help. [$-en]1526.928 -> 1,526.928(output) -> 1,526.928(expected) | [$-de]1526.928 ->1,526.928(output) -> 1.526,928(expected,comma is a dot and vice-versa) — Gouse, Jul 11 '19 at 09:48
This might help: https://stackoverflow.com/questions/40209636/epplus-number-format/40214134#40214134 — VDWWD, Jul 11 '19 at 10:15
Hi VDWWD, I saw the link contents. It does help, but there is no way to include a culture variable inside. If I do something like [$-de]"#,##0.00" then always the output comes as "1234,546.34". This output is correct for English format. But for German (as defined by [$-de]), the output should have been "1234.546,34". This behaviour is not do-able in Excel custom formatting? — Gouse, Jul 12 '19 at 08:30

Ernie S · Accepted Answer · 2019-07-13T11:39:43.150

Since you are specifying the exact data format in the string (the yyyy-mm-dd), excel will have to respect it when it opens. AFAIK, excel does not have a true "default" format that matches what you are looking for. If you open Excel and specify the format of a cell you can set the Culture/Country but you would still have to choose the format - the first one usually uses / as the separator.

But if you want to use what is in .NET, you could use CultureInfo.DateTimeFormat to get what I think you are after:

CultureInfo c;
var dt = new DateTime(2019,7,31);

c = new CultureInfo("fr-FR");
Console.WriteLine($"{c}: {c.DateTimeFormat.ShortDatePattern}");
Console.WriteLine(dt.ToString($"{c.DateTimeFormat.ShortDatePattern} (dddd)", c.DateTimeFormat));
Console.WriteLine();

c = new CultureInfo("de-DE");
Console.WriteLine($"{c}: {c.DateTimeFormat.ShortDatePattern}");
Console.WriteLine(dt.ToString($"{c.DateTimeFormat.ShortDatePattern} (dddd)", c.DateTimeFormat));
Console.WriteLine();

c = new CultureInfo("en-EN");
Console.WriteLine($"{c}: {c.DateTimeFormat.ShortDatePattern}");
Console.WriteLine(dt.ToString($"{c.DateTimeFormat.ShortDatePattern} (dddd)", c.DateTimeFormat));
Console.WriteLine();

Will give you this in the output:

fr-FR: dd/MM/yyyy
31/07/2019 (mercredi)

de-DE: dd.MM.yyyy
31.07.2019 (Mittwoch)

en-EN: M/d/yyyy
7/31/2019 (Wednesday)

To use in excel do this:

[TestMethod]
public void Culture_Info_Data_Format_Test()
{
    //https://stackoverflow.com/questions/56985491/is-there-any-way-to-format-date-cell-in-excel-with-invariant-culture-like-in-c-s
    var fileInfo = new FileInfo(@"c:\temp\Culture_Info_Data_Format_Test.xlsx");
    if (fileInfo.Exists)
        fileInfo.Delete();

    using (var pck = new ExcelPackage(fileInfo))
    {
        var dt = new DateTime(2019, 7, 31);
        var workbook = pck.Workbook;
        var worksheet = workbook.Worksheets.Add("Sheet1");
        worksheet.Column(1).Width = 50;

        var c = new CultureInfo("fr-FR");
        var format = $"[$-fr]{c.DateTimeFormat.ShortDatePattern} (dddd)";
        worksheet.Cells[1, 1].Value = dt;
        worksheet.Cells[1, 1].Style.Numberformat.Format = format;

        c = new CultureInfo("de-DE");
        format = $"[$-de]{c.DateTimeFormat.ShortDatePattern} (dddd)";
        worksheet.Cells[2, 1].Value = dt;
        worksheet.Cells[2, 1].Style.Numberformat.Format = format;

        c = new CultureInfo("en-EN");
        format = $"[$-en]{c.DateTimeFormat.ShortDatePattern} (dddd)";
        worksheet.Cells[3, 1].Value = dt;
        worksheet.Cells[3, 1].Style.Numberformat.Format = format;

        pck.Save();

    }

}

which gives this:

Hi Ernie, thank you. This solution gave me almost what I wanted. I am having other troubles with format coming as a variable. But that is a different issue. I am also trying to find something like this for number formats. Any help would be much appreciated. Thanks again. — Gouse, Jul 15 '19 at 15:26
@Gouse Sounds like you want to use `c.NumberFormat.NumberGroupSeparator` and `c.NumberFormat.NumberDecimalSeparator` for that. You can just do string concantinations with that similar to how the date format is done above. If that doesnt help, might be best to post as a new question with more details about what you are trying to do. — Ernie S, Jul 15 '19 at 16:20

score 0 · Answer 2 · answered Jul 11 '19 at 09:25

0

Try to use below formula in another column/cell, assuming you have a date starting from A1

=TEXT(A1,"mm/dd/yyy (dddd[$-fr])")

Hope it helps!

You can change the text string type from "/" to "." or whatever you want and the language format by changing this element [$-fr]

answered Jul 11 '19 at 09:25

Rocky

950
1
7
12

Hi Rocky, I am trying to display the cell value with the defined culture-info. That means at the time of writing into the format of the cell, the culture info will be written dynamically. This means pattern "yyyy-mm-dd"(or what ever is invariant culture is) stays the same. The culture [$-de], [$-en] or [$-fr] is the variable here. With this culture info, the cell value would be shown to the all the users who would open the excel-sheet in the same format irrespective of the system.currentculture. I guess, the user would need to install the corresponding language pack, though. – Gouse Jul 11 '19 at 09:35

Is there any way to format date cell in Excel with Invariant culture like in C#

2 Answers2