Converting AD Field 'lastLogon' To Date & Time - Excel Help Forum

Maybe your like

Register
Help
Forgotten Your Password?
Remember Me?

Forum
- Today's Posts
- FAQ
- Calendar
- Community
  - Groups
- Forum Actions
  - Mark Forums Read
- Quick Links :
What's New?
Members List
Calendar
Forum Rules
Dashboard
Commercial Services

Advanced Search

Forum
Microsoft Office Application Help - Excel Help forum
Excel Programming / VBA / Macros
Converting AD Field 'lastLogon' to Date & Time

+ Reply to Thread Results 1 to 7 of 7 Converting AD Field 'lastLogon' to Date & Time

LinkBack
- LinkBack URL
- About LinkBacks
Thread Tools
- Show Printable Version
- Subscribe to this Thread…
Rate This Thread
- Current Rating
- ‎ Excellent
- ‎ Good
- ‎ Average
- ‎ Bad
- ‎ Terrible
Display
- Linear Mode
- Switch to Hybrid Mode
- Switch to Threaded Mode

07-10-2008, 12:34 PM #1 Brelin
- View Profile
- View Forum Posts
Registered User Join Date 01-31-2008 MS-Off Ver 2010 Posts 38
Converting AD Field 'lastLogon' to Date & Time

Hi All I've extracted data from Active Directory using the CSVDE command and I've been able to manipulate most of the info so that it's nice and user friendly but I'm struggling with the 'lastLogon' field. It gives a number like128601615869175000 which I believe can be converted to a date and time but I'm unsure how. I've found DOS commands and .vbs scripts that will prossibly convert it but I would ideally like an Excel forumula to sort the conversion. Has anyone got such a beast or is it not possible? Thanks for any help in advance. Brelin
Register To Reply
07-10-2008, 12:40 PM #2 TheNorm
- View Profile
- View Forum Posts
Registered User Join Date 06-24-2008 Location Cambridge UK Posts 53
The lastLogon attribute is stored in Active Directory as Integer8 (8 bytes). This means it is a 64-bit number, which cannot be handled directly by VBScript. Instead, the LDAP IADsLargeInteger interface provides HighPart and LowPart methods that break the number into two 32-bit components. The resulting value represents the number of 100 nanosecond intervals since 12:00 AM January 1, 1601. The date represented by this number is in Coordinated Universal Time (UTC). It must be adjusted by the time zone bias in the local machine registry to convert to local time. From http://www.rlmueller.net/Last%20Logon.htm
Register To Reply
07-11-2008, 03:36 AM #3 Brelin
- View Profile
- View Forum Posts
Registered User Join Date 01-31-2008 MS-Off Ver 2010 Posts 38
Thanks for the definition. Am I misreading it or can I just start dividing nanoseconds into that number and then converting that all back to dates & times? Not a quick job I can see but it's all I've got at the minute. If anyone has any additonal thoughts I'm listening. Thanks Brelin
Register To Reply
01-25-2009, 06:58 PM #4 FlyingPete
- View Profile
- View Forum Posts
Registered User Join Date 01-22-2009 Location New Plymouth, New Zealand MS-Off Ver Excel 2003 Posts 1
OK so I went looking for an answer to the same question on Friday, the closest I could find to it was this forum, but still no go, so I put some time and thought into it and came up with something, so here goes: First up I needed to figure out how to get excel to deal with the now - 1 Jan 1601 issue. Now Excel will not deal with dates any earlier than 1 Jan 1900. If you were to convert a date in Excel it would come back with a number, each whole number is the number of days from 1 Jan 1900 (which if you convert comes out as 1, 2 Jan 1900 as 2 etc.) Then I needed to know how many days there have been since 1 Jan 1601 and 1 Jan 1900, taking into account leap years (every 4th year except the century unless the century is evenly dividable by 400), the answer I came up with is 190207 days. Next up I needed to convert that pesky number of 100 nanosecond intervals since 1601 number into days, so I used the following formula: =(CellReference/10000000/3600/24) So the 10000000 is the number of 100ns periods in a second, divided by 60 to give hours, the by 24 to give days. So if you subtract 190207 from the above number you get the number of days since 1 Jan 1900, now if you convert the cell format to date it will give you the last login date So from the top, if the cell you are calculating from is A1, the the working formula is: =(A1/10000000/3600/24)-109207 Remember to change the cell format time to date. There you go!
Register To Reply
01-25-2009, 07:39 PM #5 shg
- View Profile
- View Forum Posts
Forum Expert Join Date 06-20-2007 Location The Great State of Texas MS-Off Ver 2010, 2019 Posts 40,689
Looks like FP about nailed it. Microsoft has a note about this at http://www.microsoft.com/technet/scr...lastlogon.mspx.

Entia non sunt multiplicanda sine necessitate
Register To Reply
10-14-2009, 05:32 PM #6 OldD
- View Profile
- View Forum Posts
Registered User Join Date 10-14-2009 Location Columbus, Ohio, USA MS-Off Ver Excel 2003 Posts 1
Re: Converting AD Field 'lastLogon' to Date & Time

I'm not sure what time zone you are in, but I tweaked FP's formula a bit to match 'w32tm.exe /ntte' response for my local time. The previous result kept coming back a little over 4 hours different than w32tm. I verified the below formula with 20 different 'pwdLastSet' times for one of my clients and they all matched up. (I am in the Eastern time zone, by the way). It may be a coincidence, but I'm running with it. =((A1/10000000/3600/24)-109205)-0.166673
Register To Reply
01-11-2013, 08:37 PM #7 Kip4724
- View Profile
- View Forum Posts
Registered User Join Date 01-11-2013 Location Usa MS-Off Ver Excel 2010 Posts 1
Re: Converting AD Field 'lastLogon' to Date & Time

I used the following =(((ROUNDDOWN(raw_date/10000000,0)/3600)+gmt_offset)/24)-109205 The original post had an epoch correction of 109207, but I found that the one posted by OldD, 109205 agreed with w32tm /ntte raw_date. Since I am in the Eastern US timezone and it is winter, I used -5 for the gmt_offset. Before I added the rounddown, I found that some of the times were off by 1 second compared to w32tm. The 8 dates I tried from the AD (2008-2013) all agree with w32tm to the second now. This calculation would not automatically account for DST, which is left as an exercise for the reader. NOTE: It looks like Win32tm is rounding the final seconds down by truncating the fractional seconds. H:\>w32tm /ntte 129964929448137000 150422 08:55:44.8137000 - 11/4/2012 3:55:44 AM While Excel's date/time format is rounding normally when it expresses the EXCEL date-time in characters. Truncating the fractional seconds as shown in the formula above is the best one can do unless there's a way to prevent EXCEL from rounding when it applies the format. The method shown could very occasionally still cause a 1 second discrepancy.

Last edited by Kip4724; 01-11-2013 at 08:56 PM.
Register To Reply