The Moose and Squirrel Files

March 26, 2011

Converting Unix (Epoch) Times with Excel

Filed under: Code — Tags: , , , — networknerd @ 9:56 pm

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

Advertisements

1 Comment »

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

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: