Formula to convert ticks to a date in excel

Question

I have a date of 635241312000000000 from table exported from sql. The date should be 1/1/2014. I need a formula in Excel that will convert it.

I have tried the following assuming that the date was stored in ticks:

=(((((635241312000000000/100000000)/365.25)/24)/60)/60). This gives me a date of 7/19/1900 which is wrong.

I have other dates to convert too, so I need a formula that will work in Excel.

Answer 1

I believe you are correct about it being a tick date. This particular one appears to be the number of 100ths of a nanosecond since 01/01/0000. To convert it you can use:

 =(G6*POWER(10, -7) / 60 / 60 / 24)-693593

The 693593 on the end is the number of days between the tick start date and the excel date-type start date.