none
Strange date fromat problem when exporting to Excel RRS feed

  • Question

  • I have a strange formatting problem when exporting dates to Excel. I fill a datatable with headers in a date range like this:

                for (int i = 0; i <= ts.Days; i++)
                {
                    DataColumn col = new DataColumn(string.Format("{0:dd-MM-yy}", colDate.AddDays(i)));
                    col.DataType = typeof(Int32);
                    dt.Columns.Add(col);
                }

    In the datatable the dates display correctly 01-12-16, 02-12-16, 03-12-16 etc..

    When I export to excel:

                ws.get_Range("C1", lastColumnName); //.NumberFormat = "dd-MM-yy";  THIS ONLY CHANGES / TO - NOT THE US DATE FORMAT
    
                for (int h = 0; h < dt.Columns.Count; h++ )
                {
                    ws.Cells[1, h + 1] = dt.Columns[h].ColumnName.ToUpper();               
                }
    

    I get the first 12 dates (of any month) in US format and the rest in the format that I need to the end of the month. In the "Format Cells" the format is custom ddMM-yy. What am I missing? Spent ages trying to find out why. The image shows the problem.   Columns C, D & E should be 10-12-16, 11-12-16, 12-12-16.

    Date problem
    Many thanks.


    :-( Still trying to program

    • Moved by Sabah ShariqMVP Wednesday, November 30, 2016 9:52 AM Related to Microsoft Excel
    Wednesday, November 30, 2016 9:27 AM

Answers

  • Excel has several assignment properties for what is exactly in a cell. Value, Value2, Formula, etc. You might solve it by being explicit in your assignment. Specifically, using the Value property, which is the formatted cell value (as opposed to the Value2, which is the underlying, unformatted value):

    ws.Cells[1, h + 1].VALUE = dt.Columns[h].ColumnName.ToUpper();

    Alternatively, you could convert to the DateValue and assign that. I'm more of a vb/vba guy, but I think the syntax is going to be something like:

    ws.Cells[1, h + 1].VALUE2 = application.datevalue(dt.Columns[h].ColumnName.ToUpper());

    -J

    • Marked as answer by Jonsey Thursday, December 1, 2016 4:05 AM
    Wednesday, November 30, 2016 3:36 PM
  • Excel has some in-built mechanism to convert to Date Format. It is best if you can format the dates as dd-mmm-yy like 12 Dec, 2016. The default excel installation is mm/dd/yyyy. If you enter 12/11/2016, it will assume 11th Dec 2016. But if you explicitly mention as "12th Nov, 2016" it would interpret correctly.

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by Jonsey Thursday, December 1, 2016 4:05 AM
    Wednesday, November 30, 2016 4:08 PM
    Answerer

All replies

  • Hi Jonsey,

    I am moving your thread to Excel forum for getting quick response.


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Wednesday, November 30, 2016 9:52 AM
  • Excel has several assignment properties for what is exactly in a cell. Value, Value2, Formula, etc. You might solve it by being explicit in your assignment. Specifically, using the Value property, which is the formatted cell value (as opposed to the Value2, which is the underlying, unformatted value):

    ws.Cells[1, h + 1].VALUE = dt.Columns[h].ColumnName.ToUpper();

    Alternatively, you could convert to the DateValue and assign that. I'm more of a vb/vba guy, but I think the syntax is going to be something like:

    ws.Cells[1, h + 1].VALUE2 = application.datevalue(dt.Columns[h].ColumnName.ToUpper());

    -J

    • Marked as answer by Jonsey Thursday, December 1, 2016 4:05 AM
    Wednesday, November 30, 2016 3:36 PM
  • Excel has some in-built mechanism to convert to Date Format. It is best if you can format the dates as dd-mmm-yy like 12 Dec, 2016. The default excel installation is mm/dd/yyyy. If you enter 12/11/2016, it will assume 11th Dec 2016. But if you explicitly mention as "12th Nov, 2016" it would interpret correctly.

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by Jonsey Thursday, December 1, 2016 4:05 AM
    Wednesday, November 30, 2016 4:08 PM
    Answerer
  • Thank you Asadulla. Following your comments I have changed the date format and it now works fine. Many thanks.

    :-( Still trying to program

    Thursday, December 1, 2016 4:08 AM
  • Thank you MainSleuth. Your comments have helped me understand the problem more clearly.

    :-( Still trying to program

    Thursday, December 1, 2016 4:09 AM