Converting Unix (Epoch) Times with Excel

Unix time is defined by wikipedia as “…a system for describing points in time, defined as the number of seconds elapsed since midnight Coordinated Universal Time (UTC) of January 1, 1970, not counting leap seconds.”

Unix times are used by a number of Cisco products like in callmanager call record reports, as the OSPF cryptographic sequence number, and as the time measurements were taken using snmp bulkstats.

Taking leap seconds and leap years into account can be a messy business, but excel can help simplify the calculations to normal date and time by using the  vba DateAdd function.

Unfortunately it can’t be used directly in a cell formula but you can create a macro (vba function) that can be used in a cell formula.  The vba code I used is shown below.  Note that there is a second optional parameter  UTCOffset, the number of hours from UTC , that can be used to calculate local times. If omitted you will get UTC times.

Private Const SecondsPerHour = 3600
Private Const EpochStart = "1/1/1970" '1 Jan 1970 00:00:00 UTC
Function epochconvert(epochtime, Optional UTCOffset)
    If IsMissing(UTCOffset) Then
        epochconvert = DateAdd("s", epochtime, EpochStart)
    Else
        epochconvert = DateAdd("s", epochtime + SecondsPerHour * UTCOffset, EpochStart)
    End If
End Function

Function ToEpoch(dtDate, Optional UTCOffset)
    If IsMissing(UTCOffset) Then
        ToEpoch = DateDiff("s", dtDate, EpochStart)
    Else
        ToEpoch = DateDiff("s", EpochStart, dtDate) - SecondsPerHour * UTCOffset
    End If
End Function