Date Format Changing when Exporting to Excel

Question

I've written an export to excel function in my program, which coincides with an import program too.

The import program is running some validation checks, but some of them are failing because the Excel sheet is formatting them as MM/dd/yyyy, however most of them are being stored as dd/MM/yyyy.

After looking in my exports program, I can see that in the grid, before exporting, the dates are all correctly formatted as dd/MM/yyyy, so the issue is not here.

When opening the exported Excel file, I can see that some of the dates are stored as MM/dd/yyyy, however.

The regional settings on my PC are correct, set to UK and even after checking the Excel format on the column, I can see it is set to dd/MM/yyyy.

So, what is there that could be going wrong? Why are some exported differently?

The values as seen in the grid (Correct format - See top 2 rows 'Rate One Start', 01/06/2016)

Code in the export routine

Dim formatRange As Excel.Range
formatRange = xlWorksheet.Range("F2", "F99000")
formatRange.NumberFormat = "dd/MM/yyyy"

formatRange = xlWorksheet.Range("I1", "I99000")
formatRange.NumberFormat = "dd/MM/yyyy"

formatRange = xlWorksheet.Range("J1", "J99000")
formatRange.NumberFormat = "dd/MM/yyyy"

formatRange = xlWorksheet.Range("M1", "M99000")
formatRange.NumberFormat = "dd/MM/yyyy"

formatRange = xlWorksheet.Range("N1", "N99000")
formatRange.NumberFormat = "dd/MM/yyyy"

formatRange = xlWorksheet.Range("Q1", "Q99000")
formatRange.NumberFormat = "dd/MM/yyyy"

For k As Integer = 1 To dgvExport.Columns.Count
    xlWorksheet.Cells(1, k) = dgvExport.Columns(k - 1).HeaderText
Next

Dim eStr As String = ""
Dim nStr As String = ""

Me.Cursor = Cursors.WaitCursor

For i = 0 To dgvExport.RowCount - 1
  For j = 0 To dgvExport.ColumnCount - 1
     Try
       eStr = Trim(dgvExport(j, i).Value)
       nStr = eStr.Replace(vbCr, "").Replace(vbLf, "")
       xlWorksheet.Cells(i + 2, j + 1) = nStr
     Catch
       xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value
     End Try

   Next
Next

The Excel file I exported (Notice some of the dates are dd/MM, whereas some are MM/dd)

Make sure to pass dates as date data type to Excel. Currently you are passing them as strings I think and the Excel OM speaks American and hence expects the m/d/y order when converting to true Excel dates. — jkpieterse
@jkpieterse How would I go about passing them as dates? It's weird that some are in the correct format, but not all — David
@David Not weird. VBA is converting using US (MDY) format. The dates you think are correct are really text strings with the first two digits >12. The others have been converted incorrectly -- look at first line -- 1-jun-16 vs 6-Jan-16 due to VBA converting your data according to US settings. Not sure about vb.net date vs string. — Ron Rosenfeld

David David · Accepted Answer · 2017-02-22T14:14:51

As suggested by @jkpieterse, I just needed to pass the date values as actual date datatypes. To do this, I modified the For Each loops.

Original:

For i = 0 To dgvExport.RowCount - 1
  For j = 0 To dgvExport.ColumnCount - 1
    Try
       eStr = Trim(dgvExport(j, i).Value)
       nStr = eStr.Replace(vbCr, "").Replace(vbLf, "")
       xlWorksheet.Cells(i + 2, j + 1) = nStr
    Catch
      xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value
    End Try
  Next
Next

Working version:

For i = 0 To dgvExport.RowCount - 1
  For j = 0 To dgvExport.ColumnCount - 1
     Try
       If j <> 5 AndAlso j <> 8 AndAlso j <> 9 AndAlso j <> 12 AndAlso j <> 13 AndAlso j <> 16 Then
         eStr = Trim(dgvExport(j, i).Value)
         nStr = eStr.Replace(vbCr, "").Replace(vbLf, "")
         xlWorksheet.Cells(i + 2, j + 1) = nStr
       Else
         xlWorksheet.Cells(i + 2, j + 1) = Convert.ToDateTime(dgvExport(j, i).Value).GetDateTimeFormats(Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat)
       End If
     Catch
       xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value
     End Try

  Next
Next

This means, if the index of the current column being evaluated is not one of the columns that contains a date, then export it as normal, but if it is, then convert it to a date time, where the format is the same as the current culture format for the thread.

This has worked like a dream.

Date Format Changing when Exporting to Excel

1 Answers