title: Excel Interop cell formatting of Dates
body: <p>My program creates an output in Excel.</p>
  
  <p>Some of the dates seem to be getting misinterpreted:
  <img src="https://i.stack.imgur.com/5LFJ8.png" alt="enter image description here"></p>
  
  <p>On opening the file the dates are a mixture as in the screenshot.<br>
  If I actually put the cursor in Excel's calculation box (in screenprint) and press enter the formatting of the cell reverts to the correct formatting.</p>
  
  <p>I'm using <code>Microsoft.Office.Interop.Excel</code> to move data from a <code>Datatable</code> to an <code>Excel</code> template saved in my Solution.</p>
  
  <p>Before putting the data into the cells I change each columns numberformat accordingly using a <code>switch</code>:</p>
  
  <pre><code>for(int i = 1; i &lt; NumColumns + 1; i++) {
      switch(dt.Columns[i-1].DataType.ToString()) {
          case "System.Int32":
              xlWorkSheet.Columns[i].NumberFormat = "#,##0_ ;[Red]-#,##0 ";
              break;
          case "System.String":
              xlWorkSheet.Columns[i].NumberFormat = "@";
              break;
          case "System.DateTime":
              xlWorkSheet.Columns[i].NumberFormat = "[$-809]dd mmmm yyyy;@"; //"dd-mmm-yy";
              break;
          case "System.Date":
              xlWorkSheet.Columns[i].NumberFormat = "[$-809]dd mmmm yyyy;@"; //"dd-mmm-yy";
              break;
          default:
              xlWorkSheet.Columns[i].NumberFormat = "@";
              break;
      }
  }
  </code></pre>
  
  <p>To move the values from the datatable I use nested loops:</p>
  
  <pre><code>//move header totals into the spreadsheet
  for(int i = 1; i &lt; NumColumns + 1; i++) {
      xlWorkSheet.Cells[1, i].value = dt.Columns[i - 1].ColumnName;
  }
  
  //move in the data
  DataView dv = new DataView(dt);
  dv.Sort = "Date ASC";
  
  int rowCount = 2;
  string theValue;
  try {
      foreach(DataRowView dr in dv) {//(DataRow dr indt.Rows)
          for(int i = 1; i &lt; NumColumns + 1; i++) {
              theValue = dr[i - 1].ToString();
              xlWorkSheet.Cells[rowCount, i].value = theValue;
          }
          rowCount += 1;
      }
  } catch(Exception) {
      throw;
  }
  </code></pre>
  
  <p>In the stored procedure when I populate the Datatable I've tried explicitly spelling out the type using <code>DATETIME</code> and the following:</p>
  
  <pre><code>SELECT 
  "Date" = CONVERT(DATE,b.[Date])
      ...
  </code></pre>
  
  <p>How can I make my Date data so explicit that Excel cannot misinterpret and all the desired formatting is applied ?</p>
  
  <hr>
  
  <p><strong>Edit</strong></p>
  
  <p>Next (untested) attempt at the nested loop is as follows:</p>
  
  <pre><code>int rowCount = 2;
  string theValue;
  DateTime dtm;
  DateTime d;
  try {
      foreach(DataRowView dr in dv) {//(DataRow dr indt.Rows)
          for(int i = 1; i &lt; NumColumns + 1; i++) {
              //theValue = dr[i - 1].ToString();
              //xlWorkSheet.Cells[rowCount, i].value = theValue;
              switch(dr[i - 1].GetType().ToString()) {
                  case "System.DateTime":
                      dtm = Convert.ToDateTime(dr[i - 1]);
                      xlWorkSheet.Cells[rowCount, i].value = dtm.ToOADate();
                      break;
                  case "System.Date":
                      d = Convert.ToDateTime(dr[i - 1]);
                      xlWorkSheet.Cells[rowCount, i].value = d.ToOADate();
                      break;
                  default:
                      theValue = dr[i - 1].ToString();
                      xlWorkSheet.Cells[rowCount, i].value = theValue;
                      break;
              }
  
          }
          rowCount += 1;
      }
  } catch(Exception) {
      throw;
  }
  </code></pre>