Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

LongDec2Bin()
MultiCat()
PageSetupXL4M()
PrevSheet()
RandInt()
SampleNoReplace()
SumBold()
UnixToXLTime()

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>