Excel date format using EPPlus

worksheet.Cells["YOURDATECELL_OR_YOURDATECELLRANGE"].Style.Numberformat.Format = "mm-dd-yy";

if you use the formula mentioned by taraz. do add worksheet.Calculate() in the end. reference https://epplus.codeplex.com/wikipage?title=About%20Formula%20calculation

Or instead of using formula, Alternative approach

private static decimal GetExcelDecimalValueForDate(DateTime date)
{
    DateTime start = new DateTime(1900, 1, 1);
    TimeSpan diff = date - start;
    return diff.Days + 2;
}

Reference

worksheet.Cells["A2"].Value = GetExcelDecimalValueForDate(Convert.ToDateTime('2016-04-29'));
worksheet.Cells["A2"].Style.Numberformat.Format = "mm-dd-yy";//or m/d/yy h:mm

By Default when excel saves a date field it saves it as numFormatId 14(Look at the xml files in the xls). This ensure the date formats correctly in any country when the file is opened. In Epplus mm-dd-yy translates to numFormatId 14 for excel. This will ensure that when the file is opened in any country the date will be formatted correctly based on the country's short date settings. Also noticed m/d/yy h:mm formats correctly for any country.


I agree with Yosoyke. You're probably targeting the wrong cells. You can try:

ws.Cells["A3"].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells["A3"].Formula = "=DATE(2014,10,5)";

Tags:

C#

Epplus