How to convert a FILETIME to an excel date

Question

I've got an filetime value, for example: 122327856000000000 and want to convert it to an excel date (1988-aug-23)

Payroll

Answer 1

I only could find conversion from unix time (seconds since 1970/1/1) but not for filetime (nanoseconds since 1601/01/01) so I thought up the following solution:

First convert to unix time, by subtracting the nanoseconds between 1970/1/1 and 1601/1/1 which is: 116444736000000000. The resulting value is easily converted to datetime, so the final formula is:

((CELL-116444736000000000)/10000000)/(24*60*60)+DATE(1970,1,1)

Wich is the same as:

((CELL-116444736000000000)/864000000000)+DATE(1970,1,1)

Hope it helps someone else.

Payroll

Answer 2

And if you want to go backward -- you can convert from excel to filetime:

=((CELL-DATE(1970,1,1))*86400*10000000+116444736000000000)

Linson

Answer 3

Excel DateTime is represented in Days since 1/1/1900, accurate to a Millisecond

Windows FILETIME Is represented in nanoseconds since 1/1/1601

Windows FILETIME documentation says that it is 2 32-bit Long values but you can directly substitute that for a 64-bit LongLong value in VBA.

there are 109205 whole days between FILETIME's 1/1/1601 and Excel's 1/1/1900

To Get Excel Time [Days since 1/1/1900]

=TEXT( FILETIME / ( 86400E7 ) - 109205, "M/D/YYYY hh:mm:ss.000 AM/PM")

To Get FILETIME [Nanoseconds since 1/1/1601]

=TEXT( ( CELL + 109205 ) * ( 86400E7 ), "#")

this can be done in VBA with a LongLong variable for FILETIME

    Function FileTimeToDateTime(Optional FILETIME as LongLong = 122327856000000000 ) As Variant
        FileTimeToDateTime = WorksheetFunction.Text(FILETIME / (86400E7) - 109205, _
        "M/D/YYYY hh:mm:ss.000 AM/PM")
        ' Default returns "9/23/1988 4:00:00.000 AM"
    End Function
   
    Function DateTimeToFileTime(Optional ex_Date as Date = #09/23/1988 4:00:00 AM#) As LongLong
        DateTimeToFileTime = (ex_Date + 109205) + (86400E7)
        ' Default returns "122327856000000000"
    End Function

Theodore