Converting Active Directory Last Logon Time Integer to a Readable Date and Time
If you have every used CSVDE and exported Active Directory objects, you’ve noticed the integer that represents time. Last logon time is one such value that is represented as this integer. There is a way to convert it to time and date that is human readable.
Basically the Integer 8 data type is the large value that is seen on a typical Powershell get-aduser command or csvde from Active Directory. Windows NT and Active Directory store dates as nano ticks or 100-nano second intervals from the Windows NT time epoch; which is January 1st 1601. Please make a note that PowerShell’s epoch is January 1st 0001 which could screw you up if you assume all integers are like NT’s epoch.
So let’s use an integer of “128271382742968750”. There are several methods of converting this I will cover most of them:
Command Line:
Converting logontimestamp from command line:
C:\>w32tm.exe /ntte 128271382742968750 148462 05:57:54.2968750 - 6/24/2007 8:57:54 AM (local time)
Note: that the results are in GMT and the offset of the computer’s local time is added UTC +3:00 in this example above.
PowerShell:
Converting logontimestamp from PowerShell:
PS C:\>$lastlogontimestamp = “128271382742968750” PS C:\>[DateTime]::FromFileTimeutc($lastlogontimestamp) Sunday, June 24, 2007 5:57:54 AM
To convert using the current computers offset use the code below (current computer offset UTC +3:00):
PS C:\>[DateTime]::FromFileTime($lastlogontimestamp) Sunday, June 24, 2007 8:57:54 AM
Note: If you experiment please be sure to open a new PowerShell after changes to the offset of the computer since it is read into the environment only once during powershell launch.
Excel:
Lastly converting in excel the integer to human read able time:
=A1/(8.64*10^11) – 109205
- (8.64*10^11) is the number of nanoseconds in a day divided by 100.
- 109205 is the number of days, including leap days, between 1601 and 1900.
This formula would assume that A1 contains the integer of “128271382742968750” and the cell was formatted for date and time. If so it would return:
6/24/07 5:57:54 AM
To calculate offset:
=A1/(8.64*10^11) - 109205 + time(3,0,0)
Use the above to add UTC +3 to the time to display the below format:
6/24/07 8:57:54 AM
Tags: Active Directory, AD, CMD, Excel, PowerShell, Script