Have you ever seen the timestamp format like this and wondered how it’s equivalent to the normal DateTime format?
![](https://i0.wp.com/www.kjctech.net/wp-content/uploads/2018/08/image-2.png?resize=640%2C257&ssl=1)
It’s the date/time value stored in Active Directory as the number of 100-nanosecond intervals that have elapsed since the 0 hours on January 1, 1601, until the date/time that is being stored. It’s always in UTC (Coordinated Universal Time, aka. GMT) and is often used in Properties like LastLogonTimeStamp, LastPwdSet, etc. /via Technet/
The formula that can be used in Excel is something like this:
DateTime (UTC) = Timestamp/(8.64*10^11) - 109205
![](https://i0.wp.com/www.kjctech.net/wp-content/uploads/2018/08/image-4.png?resize=469%2C189&ssl=1)
You can also use w32tm command line to do the quick conversion as well.
w32tm /ntte timestamp-value
![](https://i0.wp.com/www.kjctech.net/wp-content/uploads/2018/08/image-5.png?resize=399%2C107&ssl=1)
The time shown in the second half is for local time in PCT.
Now let’s take look how it converts in PowerShell, which is so easy.
To convert a timestamp to DateTime format in UTC.
[DateTime]::FromFileTimeUtc(TimeStamp-value)
![](https://i0.wp.com/www.kjctech.net/wp-content/uploads/2018/08/image-6.png?resize=435%2C131&ssl=1)
Local time, regardless of where you are?
[DateTime]::FromFileTime(TimeStamp-value)
![](https://i0.wp.com/www.kjctech.net/wp-content/uploads/2018/08/image-7.png?resize=426%2C79&ssl=1)
Well, that’s one hour off, comparing to using the W32TM command, blame on the summer time saving.
You can also do the conversion the other way around, getting the timestamp from a specified date/time value. For example, to get the present moment in TimeStamp,
(Get-Date).ToFileTime()
![](https://i0.wp.com/www.kjctech.net/wp-content/uploads/2018/08/image-8.png?resize=302%2C83&ssl=1)
Hope it helps.
Thank you, a great help! 🙂