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

or in a cell: DATEVALUE(“1/1/1970”)+ epochtime/86400

Comment by jeminar — December 19, 2014 @ 9:32 pm