Steven JW Kennedy

My Blog

Converting a date/time AD Attribute to readable format

Posted by Steven Kennedy on April 9, 2010

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;


WordPress Tags: ,

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,"")



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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: