#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Johannesburg, South Africa
    Posts
    186
    Rep Power
    28

    Convert to/from Unix time


    Hi,

    I am actually a Perl programmer working 99% of the time on Unix systems. The result is that a lot of times I store date and time data as Integers (Unix timestamps) - either in a file or in a database like MySQL.

    Converting date's from MySQL can easily be converted using SQL statements, so no problem there.

    The problem that I do have is with CSV formatted files I have that I want to import for our admin people in Excel. One problem though - all date and time values are in Unix time format.

    I can convert the CSV files with a Perl filter BEFORE I send them to the relevant people, but to satisfy my own curiosity I was wondering if there isn't an easy way to convert Unix time stamps in Excel - either with a formula or with some VBA.

    Any ideas?

    Thanks
    Nullum magnum ingenium sine mixtura dementiae fuit.
    [There is no great genius without some touch of madness.]
    -- Seneca
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Johannesburg, South Africa
    Posts
    186
    Rep Power
    28
    Sometimes it helps when you read the help files

    For what it's worth, here is what I discovered:

    * Excel stores the date as an integer number with a starting point of 1900-01-01

    * Unix time start on 1970-01-01

    So I calculated the difference between the two, and came up with 25569.

    Time is calculated as a fraction from date. Since I live in South Africa (GMT+2), I need to also add two hours, or 0.08333

    Asuming the Unix time stamp is in column A (A1, A2 etc), my formula now looks like this:
    Code:
    =(A1/86400)+25569.08333
    For now that seems to work, but any forther notes from the experts will be appreciated.

    Cheers
    Nullum magnum ingenium sine mixtura dementiae fuit.
    [There is no great genius without some touch of madness.]
    -- Seneca
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    1
    Rep Power
    0

    Converting Between Excel and UTC Time


    I have found the following to be accurate for my purposes. Perhaps you can improve on it.
    There is some fiddly stuff with TZs because UTC is stored as time at the meridian, but excel dates have no TZ info in my case.
    Code:
    Const secondsInADay = 86400#
    Const excelUTCDiff = 25569#
    Const timezoneAdjust = 10# * 3600#
    ' utcDate = 1970-01-01 + seconds to current date + TZ offset
    ' excelDate = 1900-01-01 + days to current date + seconds/seconds in a day
    ' -10 * 3600 = timezone offset in seconds for Australia
    ' 86400 = seconds in a day
    ' 25569 = days b/w 1/1/1900 (excel epoch) and 1/1/1970 (unix epoch)
    
    Public Function UtcDateToExcelDateSerial(ByVal utcDate As Double) As Double
        '' Return an excel date serial equivalent to a unix UTC date
        Dim retVal As Double
        retVal = ((utcDate + timezoneAdjust) / secondsInADay) + excelUTCDiff
        UtcDateToExcelDateSerial = retVal
    End Function
    Public Function ExcelDateSerialToUtcDate(ByVal excelDate As Double) As Double
        Dim retVal As Double
        Dim numDays As Double
        Dim timePart As Double
        'excel date for 1/1/2009T01:01:01 = 39814.042361
        ' 39814 days since 1/1/1900 + .42361 of a day
        numDays = Math.Round(excelDate, 0)
        timePart = (excelDate - numDays) * secondsInADay    
        retVal = (((numDays - excelUTCDiff) * secondsInADay) + timePart) - (10# * 3600#)
        ExcelDateSerialToUtcDate = retVal
    End Function
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Johannesburg, South Africa
    Posts
    186
    Rep Power
    28
    Originally Posted by cargo
    I have found the following to be accurate for my purposes. Perhaps you can improve on it.
    There is some fiddly stuff with TZs because UTC is stored as time at the meridian, but excel dates have no TZ info in my case.
    Thanks - I will be testing this soon :-)
    Nullum magnum ingenium sine mixtura dementiae fuit.
    [There is no great genius without some touch of madness.]
    -- Seneca

IMN logo majestic logo threadwatch logo seochat tools logo