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)";