Excel date format using EPPlus

Question

I'm having trouble with format my cells to Date.

FileInfo info = new FileInfo(path);
using (ExcelPackage package = new ExcelPackage(info))
{
      ExcelWorksheet ws = package.Workbook.Worksheets.Add(sheetName);
      ws.Cells[3, 1].Style.Numberformat.Format = "yyyy-mm-dd";
      ws.Cells["A3"].Formula = "=DATE(2014,10,5)";
}

Output from this in Excel: 41 917,00

Why is this not working?

Lenny

try with capital MM -> yyyy-MM-dd – Henden

It is still same output – Lenny

Are you very sure you target the right cells to format? Cause when I use this format to my data-cells it works ! – Araldo

I was trying to include an AM/PM marker in my file, and noticed that the standard datetime formats used in C# didn't seem to apply. Turns out that I had to use the format that Excel would expect, NOT the C# standard. This answer helped me locate which format I could use. – Lavena

I found that to get am/pm you have to put in a format like this MM-dd-yyyy HH:mm am/pm that am/pm is actually part of the format, the output will be either am or pm. – Literally

Answer 1

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

Graeme

ws.Cells["A3"].Style.Numberformat.Format = "yyyy-mm-dd"; This seems to be working fine to convert the number to a date. The formula is not needed. – Morganatic

Answer 2

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.

Plexus

You can also use the built-in DateTime.ToOADate(); – Cherrylchersonese

DateTime.ToOADate() keep the time parts! – Cashew

How does your code differentiate between month and minute. month is capital M, Also and this is the most important point. Why is month before day? if you use month first you will cause massive issues with any system that uses dates. because they won't be able to tell if 7/6/2021 is the 6th month or the 7th month. The standard is Day/Month/Year. as you count 1s then 10s then 100s – Hotshot

Searched EVERYWHERE for this! This is the only way to take a datetime, write just the date portion to a cell, AND have it recognized as a Date in excel, which means the user's regional settings are used for display, NOT the format string mm-dd-yy that you passed. – Memoir

Setting the format to mm-dd-yy solved my issue to generate the excel with date field – Melia

Answer 3

var dateColumns = from DataColumn d in dt.Columns
                  where d.DataType == typeof(DateTime) || d.ColumnName.Contains("Date")
                  select d.Ordinal + 1;

foreach (var dc in dateColumns)
{
    worksheet.Cells[2, dc, rowCount + 2, dc].Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss AM/PM";
}

it will format all the columns with header Date to specific format given/ provided

Boost

Excel doesn't have DataColumn classes. You assume the sheet is generated from a DataTable and that the table and sheet column order is exactly the same. – Landowska

How do it using DataTable with LoadFromDataTable method ? – Herbal

How does your code differentiate between month and minute. month is capital M, Also and this is the most important point. Why is month before day? if you use month first you will cause massive issues with any system that uses dates. because they won't be able to tell if 7/6/2021 is the 6th month or the 7th month. The standard is Day/Month/Year. as you count 1s then 10s then 100s – Hotshot

Answer 4

I was having the same problem with my CSV to be transformed. I was able to do this in a little different manner.

private string ConvertToExcel(string CSVpath, string EXCELPath)
    {
        try
        {
            string Filename = System.IO.Path.GetFileNameWithoutExtension(CSVpath);
            string DirectoryName = System.IO.Path.GetDirectoryName(CSVpath);
            EXCELPath = DirectoryName + "\\" + Filename + ".xlsx";

            string worksheetsName = "Report";
            bool firstRowIsHeader = false;

            var format = new OfficeOpenXml.ExcelTextFormat();
            format.Delimiter = '|';
            format.EOL = "\n";

            using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(EXCELPath)))
            {
                string dateformat = "m/d/yy h:mm";
                //string dateformat = System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

                OfficeOpenXml.ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
                worksheet.Cells["A1"].LoadFromText(new System.IO.FileInfo(CSVpath), format, OfficeOpenXml.Table.TableStyles.Medium2, firstRowIsHeader);

                worksheet.Column(3).Style.Numberformat.Format = dateformat;
                worksheet.Column(5).Style.Numberformat.Format = dateformat;
                worksheet.Column(6).Style.Numberformat.Format = dateformat;
                worksheet.Column(20).Style.Numberformat.Format = dateformat;
                worksheet.Column(21).Style.Numberformat.Format = dateformat;
                worksheet.Column(22).Style.Numberformat.Format = dateformat;




                package.Save();
            }
        }
        catch (Exception ex)
        {
            //DAL.Operations.Logger.LogError(ex);
            Console.WriteLine(ex);
            Console.Read();
        }
        return EXCELPath;
    }

Hey

Answer 5

Generic solution which takes IEnumerable (data) it loops through the properties of the generic object finds which is of DateType or nullableDate Type and applies formatting:

   //set the list of dateColumns which will be used to formate them
            List<int> dateColumns = new List<int>();

            //get the first indexer
            int datecolumn = 1;

            //loop through the object and get the list of datecolumns
            foreach (var PropertyInfo in data.FirstOrDefault().GetType().GetProperties())
            {
                //check if property is of DateTime type or nullable DateTime type
                if (PropertyInfo.PropertyType == typeof(DateTime) || PropertyInfo.PropertyType == typeof(DateTime?))
                {
                    dateColumns.Add(datecolumn);
                }
                datecolumn++;
            }

            // Create the file using the FileInfo object
            var file = new FileInfo(outputDir + fileName);

            //create new excel package and save it
            using (var package = new ExcelPackage())
            {
                //create new worksheet
                var worksheet = package.Workbook.Worksheets.Add("Results");


                // add headers
                worksheet.Cells["A1"].LoadFromCollection(data, true);

                //format date field 
                dateColumns.ForEach(item => worksheet.Column(item).Style.Numberformat.Format = "dd-mm-yyyy");

                // auto size columns
                worksheet.Cells.AutoFitColumns();

                //save package
                package.SaveAs(file);
            }

Falkner

nice dynamic solution, awesome thanks. – Harlandharle

Answer 6

You can try, If you want using AM/PM

   worksheet.Cells[1].Style.Numberformat.Format = "dd/MM/yyyy  HH:mm:ss AM/PM";

Decasyllable

Answer 7

Make sure your cell width is large enough to display your date! This is the problem if the cell displays ### symbols.

A simple fix for this is to autofit the cell width in your worksheet:

ws.Cells.AutoFitColumns();

Complete example with passing a DateTime object:

ws.Cells[3, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[3, 1].Value = new DateTime(2014,10,5);
ws.Cells.AutoFitColumns();

---

For advanced formatting, look at https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68.

Keep in mind NOT to localize reserved characters of the numberformat code into another language: Write yyyy for the year, not jjjj. If you want to format a number and want the decimal separator, write 0.00, not 0,00.

(Posted this as I keep stumbling over this problem and this question is the first search result.)

Plasmasol

Thanks. Nice Answer For Value Field not Formula. – Jesselton

Answer 8

Following on from the very good Generic solution which takes IEnumerable.. answer we had to go a step further and display different date formatting for different properties. Fro example some columns needed to be displayed as dd/MM/yyyy and others as dd/MM/yyyy hh:mm.

So we added a DisplayFormat annotation with a DataFormatString (representing a DateTime format) to our properties like this:

using System.ComponentModel.DataAnnotations;
...
[DisplayName("Download Date")]
[DisplayFormat(DataFormatString = "dd/MM/yyyy hh:mm")]
public string DownloadDate { get; set; }
...

And then borrowing from Generic solution which takes IEnumerable.. we pulled out the date format string from the DisplayFormat annotation when iterating the properties of the data object:

public void FormatDateColumns(ExcelWorksheet worksheet, IEnumerable<IResult> data)
{
    // Dictionary 'key' contains the Index of the column that contains DateTime data
    // Dictionary 'value' contains the DateTime format for that column
    Dictionary<int, string> dateColumns = new Dictionary<int, string>();
    int dateColumnIndex = 1;

    // find all the DateTime/DateTime? columns in the data object 
    foreach (var PropertyInfo in data.FirstOrDefault().GetType().GetProperties())
    {
        if (PropertyInfo.PropertyType == typeof(DateTime) || PropertyInfo.PropertyType == typeof(DateTime?))
        {
            string dateTimeFormat = Constants.DefaultDateTimeFormat;

            // attempt to get a DataFormatString from a DisplayFormat annotation which may be decorating the Property
            // looking for an annotation something like [DisplayFormat(DataFormatString = "dd-MM-yyyy hh:mm")] 
            if (PropertyInfo.CustomAttributes != null)
            {
                var dislayFormatAttribute = PropertyInfo.CustomAttributes.Where(x => x.AttributeType.Name == "DisplayFormatAttribute").FirstOrDefault();
                if (dislayFormatAttribute != null && dislayFormatAttribute.NamedArguments != null && dislayFormatAttribute.NamedArguments.Count > 0)
                {
                    var displayFormatArg = dislayFormatAttribute.NamedArguments.First();
                    if (displayFormatArg != null && displayFormatArg.TypedValue != null && displayFormatArg.TypedValue.Value != null)
                    {
                        // NOTE: there is probably an easier way to get at this value?
                        dateTimeFormat = displayFormatArg.TypedValue.Value.ToString();
                    }
                }
            }

            dateColumns.Add(dateColumnIndex, dateTimeFormat);
        }
        dateColumnIndex++;
    }

    if (dateColumns.Count > 0)
    {
        // apply the formatting
        dateColumns.ToList().ForEach(item => worksheet.Column(item.Key).Style.Numberformat.Format = item.Value);
    }
}

Ashlaring

Answer 9

I wanted to add that the setting of the format was the solution for me. But, I could not get it to work until I set the value property to a DateTime object and not a string. That was the key to making it all work.

Egwin

Answer 10

I had a similar issue, and even though I was correctly setting the date and applying the proper number format to the cell containing the date, I was seeing the numeric representation of the date.

Turns out that after that, I applied a style, that effectively reset my format.

The code was something like:

ws.Cells["A3"].Style.Numberformat.Format = 
System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
ws.Cells["A3"].Value = New DateTime(2021, 10, 15, 23, 16, 0).ToOADate();

and later, I had:

ws.Cells("A3").StyleName = colStyle //colstyle is a style created earlier

To fix that, I needed to apply the NumberFormat.Format after setting the style.

Denitrify

Answer 11

Some news:

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

Literacy

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you’ve made. – Flange