yyyy-MM-dd date format in Export to Excel

Question

Is there any way for getting date alone from UniversalSortableDateTimePattern using this in Export to Excel function. The Excel doesn't accepts the date format..
I need to display as yyyy-MM-dd. Any suggestions ll be helpful ? My code as

 dtASalesDrivers.Columns.Add(new System.Data.DataColumn("Start Date", typeof(String)));
DateTime dt = Convert.ToDateTime(txtATrendStartDate.Text);
 drASalesDrivers[2] = string.Format("{0:yyyy-MM-dd}", dt);
dtASalesDrivers.Rows.Add(drASalesDrivers);

Edit :

            DateTime dt = Convert.ToDateTime(txtATrendStartDate.Text);
            drASalesDrivers[2] = string.Format("{0:u}", dt);

This displays both date and time. Is there any way for displaying only date ???

Answer 1

here is my code, I realize it by following code:

 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range excelRange;
excelRange = worksheet.get_Range("H2", System.Reflection.Missing.Value);
excelRange.NumberFormat = "yyyy-MM-dd;@";

Answer 2

I think that is to do with your computers settings.

If you open up a new instance of Excel and type 2012-07-24 and navigate away from the cell, it will instantly change to 24/07/2012, it is more your computers regional settings than anything to do with your code.

You can however format the cell to yyyy-mm-dd;@ so you may need to do this programmatically and not change anything else, I think that this may work:

DateTime dt = Convert.ToDateTime(txtATrendStartDate.Text);
drASalesDrivers[2] = string.Format("{0:u}", dt);
drASalesDrivers[2].NumberFormat = "yyyy-mm-dd;@";

But I haven't tested it

I was doing some messing around and some example code is below:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace ConsoleApplication19
{
    class Program
    {
        static void Main(string[] args)
        {
            Application _excelApp = new Application();
            Workbook workbook = _excelApp.Workbooks.Open(@"C:\test\New Microsoft Excel Worksheet.xlsx");

            Worksheet sheet = (Worksheet)workbook.Sheets[1];
            Range excelRange = sheet.get_Range("A1");

            //If you comment the next line of code, you get '24/07/2012', if not you get '2012-07-24'
            excelRange.NumberFormat = "yyyy-mm-dd;@";

            excelRange.Value2 = "2012-07-13";

            workbook.Save();
            workbook.Close(false, @"C:\test\New Microsoft Excel Worksheet.xlsx", null);
            Marshal.ReleaseComObject(workbook);
        }
    }
}

Also, string.Format("{0:u}", dt); will indeed return a date and a time, dt.ToString("yyyy-MM-dd"); will return your date.

The alrernative is to change your Short date setting on your computer to yyy-MM-dd like so:

This will make your date appear how you want by default on Excel on your computer, but it will look differently on different computers.