Convert lastlogon timestamp to readable format using W32TM and Excel
Most of the active directory admin have received a request to extract the last logon time for the list of users and computers from AD, we can use the CSVDE command to extract the lastLogon attribute value however from CSVDE output the lost logon attribute value would not be the readable format or usuable date/time format, and you can’t understand the format because it’s a UTC format
w32tm /ntte (lastLogon attribute value)
C:>w32tm /ntte 127281844863301000
147316 22:48:06.3301000 – 5/5/2004 4:18:06 AM (local time)
It’s very difficult to use this command for bulk extract, we can convert this in Excel itself using below procedure.
You can use below formula to convert UTC format to normal readable format in Excel.
=IF(A1>0, DATE(1601,1,1) +A1 /600000000/1440,””)
A1 is the cell that contains the Timestamp.
Yes, in Excel itself you can convert lastlogon and lastlogontimestamp to a normal readable format no need for additional script and command, we can use this simple procedure to extract lastlogon timestamp from active directory without using a script, more about CSVDE
You can also use our online windows timestamp converter