Convert an Entire Column in Unix Epoch Time into Excel Date (YYYY-MM-DD) form VBA

Question

I have a CSV that has Unix Epoch Time. While there are examples of converting epoch time into date format with formulas, I could not find any VBA example that converted an entire column (size can vary) of epoch times into a yyyy-mm-dd form. What I am trying to do in the macro is

Sub FormatTimeStamp()  
    Dim tbl As ListObject
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        For Each tbl In sht.ListObjects
            For Each dateCol In tbl.ListColumns
                If InStr(dateCol.Name, "timestamp") > 0 Then
                    dateCol.DataBodyRange.NumberFormat = "yyyy-mm-dd"
                End If
            Next dateCol
        Next tbl
    Next sht 
End Sub

1. Get a list of all sheets in the workbook (Except the active sheet)
2. In each sheet, Read the column name which has Epoch Times (Can I refer to a column name in VBA?)
3. Create a New Column next to it with YYYY-MM-DD form (How do I create a new column name to the left hand side of the epoch time)
4. For each item in Epoch time, create the YYYY-MM-DD form equivalent in its own column

Answer 1

You need just minor changes in your code. See the explanations in the code comments.

Option Explicit 'see http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices

Sub FormatTimeStamp()
    Dim tbl As ListObject
    Dim sht As Worksheet
    Dim newCol As Long

    For Each sht In ThisWorkbook.Worksheets
        For Each tbl In sht.ListObjects
            newCol = tbl.ListColumns("timestamp").Range.Column - tbl.ListColumns(1).Range.Column + 1
              'calculate the position of timestamp column whithin the table
              'note: .Column returns the column number relative to the sheet but we need
              '      the column number relative to the table! Therefore we subtract the
              '      column number of the first column of the table and add 1 to
              '      obtain the position of the column relative to the table.

            tbl.ListColumns.Add Position:=newCol
              'add a new column before the timestamp column

            tbl.HeaderRowRange(newCol).Value = "date from timestamp"
              'add a header name for the new column (optional)

            tbl.DataBodyRange(1, newCol).Formula = "=([timestamp]/86400)+DATE(1970,1,1)"
              'calculate the date from the timestamp with a formula
              'note: we only need to fill the first row of a column
              '      the formula gets copied down automatically by Excel
              '      this works only within tables.
              '      In a normal sheet range we would need to loop throug all rows,
              '      to fill in the formula in each cell of the entire column.

            tbl.ListColumns(newCol).DataBodyRange.NumberFormat = "yyyy-mm-dd"
              'change the cell format of the data range to international date format
        Next tbl
    Next sht
End Sub

For more info see The VBA Guide To ListObject Excel Tables.

---

Alternative: If you want empty timestamps to be an empty date too, then use this formula instead.

=IF([timestamp]<>"",([timestamp]/86400)+DATE(1970,1,1),"")

Answer 2

You can try a couple of methods - the first method needs a new column and inserts the formula to convert epoch time to an Excel date and then applies a format to get 'yyyy-mm-dd'. This way is pretty simple - originally I was converting the new date with TEXT function but if you want to apply NumberFormat instead then that is the way I left it below un-commented.

The second method reads the column of epoch times from a Range to a Variant and then does the calculation on the variant array before writing the converted data back to the sheet. With the second method you could use it to either have a new column of data or overwrite the original epoch time data.

You'd have to adapt the code below to your circumstances with ListObjects etc but the principle would be the same:

Option Explicit

Sub Method1()

    Dim rngEpochTimeData As Range

    ' get times
    Set rngEpochTimeData = ThisWorkbook.Worksheets("Sheet1").Range("A2:A11")

    ' set range offset by 1 column with converting formula with RC notation
    ' set as text
    'rngEpochTimeData.Offset(0, 1).FormulaR1C1 = "=TEXT((RC[-1]/86400)+DATE(1970,1,1),""yyyy-mm-dd"")"

    ' set as date and also set number format
    With rngEpochTimeData.Offset(0, 1)
        .FormulaR1C1 = "=(RC[-1]/86400)+DATE(1970,1,1)"
        .NumberFormat = "yyyy-mm-dd"
    End With

End Sub

Sub Method2()

    Dim rngEpochTimeData As Range
    Dim varTimeData As Variant
    Dim lngCounter As Long
    Dim lngEpochTime As Long

    ' get times
    Set rngEpochTimeData = ThisWorkbook.Worksheets("Sheet1").Range("A2:A11")

    ' set range data to array
    varTimeData = rngEpochTimeData.Value

    ' iterate array and do conversion
    For lngCounter = LBound(varTimeData, 1) To UBound(varTimeData, 1)
        lngEpochTime = varTimeData(lngCounter, 1)
        varTimeData(lngCounter, 1) = CDate((lngEpochTime / 86400) + 25569)
    Next lngCounter

    ' write to range
    With rngEpochTimeData.Offset(0, 2)
        .Value = varTimeData
        .NumberFormat = "yyyy-mm-dd"
    End With

End Sub

Example input and output: