Microsoft have a knowledgebase article posted on this KB555936. This works for one off’s but is not so good if you have multiple conversions to make.
Essentially you use the command w32tm.exe, via a command prompt, as in;
This converted the AD lastLogonTimestamp entry of 129082975227569000 to the equivalent GMT date/time
As I said this work fine for a one off but if you have multiple conversions then you’ll need something like an Excel formula. Well, as it happens we have just the thing, thanks to a someone called Ellie;
=IF(A2>0,A2/(8.64*10^11) – 109205,"")
In the formula the source time data is in cell A2. It does a quick check to see if a number exists in the cell. If it does it then computes the equivalent Date/Time.
- 8.64*10^11, sometimes seen as 864000000000 is the number of nanoseconds in a day divided by 100
- 109205 is the number of days between 1/1/1601 and 1/1/1900, including leap days
- 1/1/1601 is the start date used for AD date/time attributes
- 1/1/1900 is the start date/time used by Excel
To have the result appear in a particular time zone date/time then you’re going to have to modify the 109205 entry. Take the time difference between GMT and your target time zone and divide by 24 to produce a time zone offset.
=IF(A2>0,A2/(8.64*10^11) – 109205 +/- <time zone offset>,"")
For the East Coast this would look like;
=IF(A2>0,A2/(8.64*10^11) – 109205 – .208333333,"")
which could be simplified to;
=IF(A2>0,A2/(8.64*10^11) – 109205.208333333,"")
Example:
Note! Day light savings! There’s a couple of week in Spring and Fall when the difference between GMT and US Time Zones are different by an hour. For the East Coast the difference is 4 hours NOT 5. You’d have to use 0.166666666 to get the correct time then.
Steven JW Kennedy 