General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions


XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Converting Unix timestamps to XL date/times

Unix bases its time values on the number of seconds since midnight (00:00:00) on 1 January 1970. The conversion to XL date/time form is trivial. If A1 contains the UNIX time value, the XL date/time is:

    =DATE(1970,1,1) + A1/86400

Writing a UDF is a little more complicated, since XL can have either of 2 base dates (0 January 1900 or 1 January 1904). This macro will convert it. Note that since VBA uses the 1900 date system, the 1904 adjustment is only made if the function is called from a worksheet.

   Public Function UNIXtoXL(dUTime As Double) As Date
     Const cdCONVERT As Double = 86400
     Const cdADJ1904 As Double = 1462
     Const cdBASEDATE As Double = 25569 '1/1/1970
     UNIXtoXL = cdBASEDATE + dUTime / cdCONVERT - _
        (cdADJ1904 * ActiveWorkbook.Date1904 * _
        (TypeName(Application.Caller) = "Range"))
   End Function

Valid XHTML 1.1Valid CSSMade on a Macintosh

Quick Links

Not familiar with User Defined Functions (UDFs)? See David McRitchie's Getting Started With Macros

Where to put your code

About Event Macros>