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

## Brelin

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

----------


## TheNorm

> 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

----------


## Brelin

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

----------


## FlyingPete

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  :Smilie: 

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!

----------


## shg

Looks like FP about nailed it. Microsoft has a note about this at http://www.microsoft.com/technet/scr...lastlogon.mspx.

----------


## OldD

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

----------


## Kip4724

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.

----------

