exceldate

How to convert a FILETIME to an excel date

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

Solution

  • 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.