Epplus c# wrong date format for Cell

Question

I have a template in Excel in which some Cells are formated to date. My Problem is that the Excel Cell isn't correctly updated after my code is executed.

globalTemplateSheet.Cells[3, 13].Value = "01." + date.Month + "." + date.Year;

I read something about Style.Numberformat.Format but the Excel Range never gets updated

Example for my problem

Cell L3 is correct, Cell M3 should be the same, but to get the same as L i have to use the button "Enter" Picture

How can i format this Cell with the right Date.

Answer 1

ws.Cells[3,13].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[3,13].Formula = "=DATE(2016,12,1)";

Credits go here

Answer 2

This should work, but your locale settings also are important.

First, set the cell Value to DateTime:

globalTemplateSheet.Cells[3, 13].Value = new DateTime(date.Year, date.Month, 1);

Second, the format in your provided screenshots:

globalTemplateSheet.Cells[3, 13].Style.Numberformat.Format = "mmm. yy";