Search and remove "00:00" values from each column

Question

I am trying to get my codes to go through each DateTime column and search for cells that contain "00:00" as hours and minutes. If the value exists, remove 00:00 and only leave the date. For the cells that don't contain that value, leave the cells as they are. For example, if "3/22/2017 00:00", then format it to "3/22/2017". Otherwise, if "3/22/2017 09:16", leave it alone. Thank you in advance!

dataGrid.DataSource = dataSet.Tables(0)
dataGrid.DataBind()
ws.Cells(1, 1).LoadFromDataTable(dataGrid.DataSource, True)
Dim data = dataGrid.DataSource
Dim columnCount = data.Columns.Count
For i = 0 To columnCount - 1
    If data.Columns(i).DataType Is GetType(DateTime) Then
        If Not data.Columns(i).ToString.Contains("00:00") Then
            ws.Column(i + 1).Style.Numberformat.Format = "mm/dd/yyyy hh:mm"
        Else
            ws.Column(i + 1).Style.Numberformat.Format = "mm/dd/yyyy"
        End If
    End If
Next

Answer 1

This can be accomplished by:

1. Setting a default format for all DateTime columns
2. Only overriding the default format for values that meet your criteria.

Here's a simple helper method that handles multiple DateTime columns:

Sub SetDateTimeStyles(ByRef data As DataTable, ByRef ws As ExcelWorksheet)
    ' Track DateTime columns to override default column style
    Dim dateTimeColumns = New List(Of Integer)()

    ' Set column format
    Dim columnCount = data.Columns.Count
    For i = 0 To columnCount - 1
        If data.Columns(i).DataType Is GetType(DateTime) Then
            Dim epPlusColumn = i + 1
            ws.Column(epPlusColumn).Style.Numberformat.Format = "mm/dd/yyyy hh:mm"
            dateTimeColumns.Add(epPlusColumn)
        End If
    Next

    ' Header row exists; set to 1 if no header row
    Dim rowOffset = 2
    Dim rowCount = data.Rows.Count

    ' Only set cell format when hour and minute are **both** zero
    For i = 0 To rowCount - 1
        For Each dateTimeColumn In dateTimeColumns
            Dim value As DateTime = data(i)(dateTimeColumn - 1)
            If value.Hour = 0 AndAlso value.Minute = 0 AndAlso value.Second = 0 Then
                ws.Cells(i + rowOffset, dateTimeColumn) _
                    .Style.Numberformat.Format = "mm/dd/yyyy"
            End If
        Next
    Next
End Sub

And call it when creating the Excel file:

dataGrid.DataSource = dataSet.Tables(0)
dataGrid.DataBind()
ws.Cells(1, 1).LoadFromDataTable(dataGrid.DataSource, True)
Dim data As DataTable = dataGrid.DataSource
SetDateTimeStyles(data, ws)

Result: