Active Directory stores date/time values as the number of 100-nanosecond intervals that have elapsed since the 0 hour on January 1, 1601 until the date/time that is being stored. The time is always stored in UTC (Coordinated Universal Time, which used to
be called Greenwich Mean Time, or GMT) in the Active Directory. Some examples of Active Directory attributes that store date/time values are
LastLogonTimestamp, accountExpires, and
LastPwdSet. In order to obtain the date/time value stored in these attributes into a standard format, some conversion is required. This article describes how this conversion can be done.
Here I have taken advantage of Excel. Find the below example: =IF(C2>0,C2/(8.64*10^11) - 109205,"")
Normally we can convert with w32tm; find the below example:-
w32tm.exe /ntte 128271382742968750
148462 05:57:54.2968750 - 6/24/2007 8:57:54 AM (local time)
on a computer that is in a time zone three hours ahead of Greenwich Mean Time (GMT +3:00). Notice that the first half of the output displays the time in GMT (05:57:54) and then converts it by adding the Time Zone Offset (8:57:54).
For more references:-
How Can I Get a List of All the Objects that have been added to Active Directory Since a Specified Date?
The Excel formual in this article works great for any Active Directory Integer8 date (represented by a 64-bit integer), including accountExpires, pwdLastSet, and lastLogonTimeStamp. You divide the AD Integer8 value by 8.64 * 10^11 to convert the 100-nanosecond intervals into days. The constant 109205 in the formula adjusts for the number of days between January 1, 1601 (the "zero" date for integer8 values in AD) and December 31, 1899 (the "zero" date for Excel). But the number of days between the dates is actually 109206. Excel has a bug. If you check the date that corresponds to 1 in Excel, you get January 1, 1900. You find that 59 corresponds to February 28, 1900, 60 corresponds to February 29, 1900, and 61 corresponds to March 1, 1900. But 1900 was not a leap year. There was no February 29, 1900. To adjust for this bug, you use 109205 in the formula.
Thanks Richard for the awesome info
Thankz richard, always helpful
Ed Price - MSFT edited Revision 6. Comment: Technology in title
Very Good Post ...