title: From DataBase to Excel : date format issue
body: <p>I'm currently trying to export some data from my database to an Excel file. Everything goes fine except for one column. </p>
  
  <p>I've got two date fields in my targeted table (start and end date) which I want to display in my Excel sheet. The thing is, the end date is correctly displayed (dd/mm/yyyy) while my start date isn't (mm/dd/yyyy). Their definition are absolutely the same at the database level.</p>
  
  <p>Here's how I'm building my DataTable :</p>
  
  <pre><code>    public System.Data.DataTable ExportLastChangesToExcel()
      {
  
  
          List&lt;HD_DISCOUNTS&gt; listDiscount = (from d in dbHosp.HD_DISCOUNTS where d.TO_EXTRACT == "N" orderby d.EXTRACT_TEXT descending select d).ToList();
  
  
          System.Data.DataTable table = new System.Data.DataTable();
          table.Columns.Add("Condition Type", typeof(string));
          table.Columns.Add("Sales organisation", typeof(string));
          table.Columns.Add("Distribution Channel", typeof(string));
          table.Columns.Add("Customer", typeof(string));
          table.Columns.Add("Customer Name", typeof(string));
          table.Columns.Add("Material", typeof(string));
          table.Columns.Add("Material name", typeof(string));
          table.Columns.Add("Amount", typeof(string));
          table.Columns.Add("Unit", typeof(string));
          table.Columns.Add("C..", typeof(string));
          table.Columns.Add("Valid From", typeof(string));
          table.Columns.Add("Valid To", typeof(string));
          table.Columns.Add("Valid Action", typeof(string));
  
          var allCust = (from v in dbCust.CUSTOMER_MASTER where v.INCLUDE_INTO_HD == "Y" select v).ToList();
  
          foreach (HD_DISCOUNTS disc in listDiscount)
          {
              string compareId = disc.ID_HOSP.ToString();
              CUSTOMER_MASTER cust = (from h in allCust where h.SOLDTOPARTY.TrimStart('0') == compareId select h).FirstOrDefault();
              DIM_PRODUCT prod = (from p in db.DIM_PRODUCT where p.P_KEY_AZ == disc.ID_PRODUCT select p).FirstOrDefault();
              DIM_PRODUCT_SAP sap = (from s in db.DIM_PRODUCT_SAP where s.P_KEY_AZ == prod.P_KEY_AZ &amp;&amp; s.P_TYPE_SAP == "Domestic" select s).FirstOrDefault();
  
              table.Rows.Add("ZD22", "BE10", "1", cust.SOLDTOPARTY, cust.SOLDTOPARTY_DESC, sap.P_KEY_SAP.ToString(), prod.P_DESC_AZ, disc.DISCOUNT.ToString(), "%", "A", disc.START_DATE.Value.ToString("dd/MM/yyyy"), disc.END_DATE.Value.ToString("dd/MM/yyyy"), disc.EXTRACT_TEXT);
  
          }
  
  
          return table;
  
      }
  </code></pre>
  
  <p>I have debugged and start date should be well displayed. Here's the code I use to build my Excel : </p>
  
  <pre><code>    protected void btnExport_Click(object sender, EventArgs e)
      {
          Microsoft.Office.Interop.Excel.Application excel;
          Microsoft.Office.Interop.Excel.Workbook worKbooK;
          Microsoft.Office.Interop.Excel.Worksheet worksheet;
          Microsoft.Office.Interop.Excel.Range celLrangE;
  
          try
          {
              excel = new Microsoft.Office.Interop.Excel.Application();
              excel.Visible = false;
              excel.DisplayAlerts = false;
              worKbooK = excel.Workbooks.Add(Type.Missing);
  
              worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
              worksheet.Name = "Sheet1";
  
              System.Data.DataTable tab = ExportLastChangesToExcel();
              Range excelRange = worksheet.get_Range("K1");
  
              excelRange.NumberFormat = "dd/mm/yyyy;@";
              int rowcount = 2;
  
              foreach (DataRow datarow in tab.Rows)
              {
                  rowcount += 1;
                  for (int i = 1; i &lt;= tab.Columns.Count; i++)
                  {
  
                      if (rowcount == 3)
                      {
                          worksheet.Cells[1, i] = tab.Columns[i - 1].ColumnName;
                          worksheet.Cells.Font.Color = System.Drawing.Color.Black;
  
                      }
  
                      worksheet.Cells[rowcount-1, i] = datarow[i - 1].ToString();
                      //worksheet.Cells[i, 11].NumberFormat = "@";
  
                      if (rowcount &gt; 3)
                      {
                          if (i == tab.Columns.Count)
                          {
                              if (rowcount % 2 == 0)
                              {
                                  celLrangE = worksheet.Range[worksheet.Cells[rowcount, 1], worksheet.Cells[rowcount, tab.Columns.Count]];
  
  
                              }
  
                          }
                      }
  
                  }
  
              }
              celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowcount, tab.Columns.Count]];
              celLrangE.EntireColumn.AutoFit();
  
              celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[2, tab.Columns.Count]];
  
              worKbooK.SaveAs("C:\\Temp\\Hospital Discount\\Genpact\\GenpactExtract.xlsx");
              worKbooK.Close();
              excel.Quit();
  
  
  
          }
          catch (Exception ex)
          {
  
          }
          finally
          {
              worksheet = null;
              celLrangE = null;
              worKbooK = null;
  
              lblResultMessage.Text = "Extract for Genpact has been successfully generated";
              lblResultMessage.Visible = true;
          }
  </code></pre>
  
  <p>As the column K is the one containing the wrong dates, I tried to format this column to get my dates well formatted, but without success.</p>
  
  <p>Any idea on what's possibly going wrong ?</p>