* note.. all that is on one line. I wrapped it because blogger was cutting it off.

That worked great, but excel choked on the dates. It turns out this is because the dates are not "normal" timestamp dates. "Normal" timestamps are based on the number of seconds since midnight on 1/1/1970. The timestamps in Active Directory in UTC format, a 64 bit number based on the number of nanosecond since 1/1/1601 divided by 100! For even more complexity, Excel's dates are based on the number of days since 1/1/1900. Arrgh.

As theoretical knowledge goes, that is all fine and good.. but how do we use it in excel?

The formula to convert from Active Directory LastLogon or LastLogonTimestamp is:

=IF(C2>0,C2/(8.64*10^11) - 109205,"")

Explanation:

C2 is the cell that contains the Timestamp.

The If() statement hides the value if the user has not logged in.

(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. (Remember, 1900 is when excel dates "start")

That's it. Paste in your formula and format it as a date, or date/time. The times returned are in GMT. All of the other solutions I saw in my google searches pointed to a vbScript solution. Please leave a comment if this helps you.

Thanks,Ellie

P.s. If you want it in Central US time (GMT-6), subtract 0.25 (That is 6 hours divided by 24 hours in a day). For Eastern time (GMT-5), subtract 0.208333333 .. (5/24).

WOW - that was really a great solution!! I, like others didn't want to write a script and create another file. This was fantastic. However, maybe I copied it incorrectly as Excel would not let me overlay the C2 cell with this formula...I had to place it in a row next to it and point to C2. Not a big thing but in case future users run into this problem.

This is the kind of solution I was thinking about, I got trouble doing the text conversion but it shouldn't be as complicated as a vba script since mathematicaly sounds so simple! Thank you very much!!!!

To fully understand the math background here is the "open-formula", which can be easily understood and customized for local time zones. As we know the Active Directory timestamp is the nanoseconds elapsed after 1601 January 1st... or as I call it this is the "Pre Jeanne d'Arc timeformat" ;-) (Yeah. Who the hell's idea was that? :-) So, to have it in readable format using Excel, we have to convert it into seconds->minutes->hours->and days, then add the 1601.01.01. date, and add/or/subtract the UTC difference hours at the end. Of course the A1 cell should be modified to be the first relevant cell used in excel. The x variable in this formula should be replaced with a number that represents the difference between the UTC and your local time. Do not give negative numbers there, just do a subtract instead. Finally you have to customize the cell format in excel to be date, and for the format choose the: 3/14/01 13:30 option for the whole column.

One other note to consider. Domain controllers do not synchronize the last logon attribute. So you will need to capture the user data from all the DCs and use the most recent logon as the last logon. See the Scripting Guy's notes: http://207.46.16.252/en-us/magazine/2006.01.scriptingguy.aspx

Secondly; please excuse the ignorance here but I really never work within Excel. I've applied this to my spread sheet but it returns "#VALUE" in the field, even after I've formatted it as a Date column. Any help would be great!

First, the formual above works great for any Active Directory Integer8 date (represented by a 64-bit integer), including accountExpires, pwdLastSet, and lastLogonTimeStamp. The constant 109205 in the formula works, but actually the number of days between January 1, 1601 (the zero date for Integer8 values in AD) and December 31, 1899 (the zero date for Excel) is actually 109206. But Excel has a bug. If you check the date that corresponds to 1 in Excel, you get January 1, 1900. You find that 59 corresponds to February 28, 1900, 60 corresponds to February 29, 1900, and 61 corresponds to March 1, 1900. But 1900 was not a leap year. There was no February 29, 1900. To adjust for this bug, you use 109205 in the formula.

HiI've encouneterd some problem:I'v done an export, but field 'last logon' mostly is empty. Some users have correct lastlogon stamp, but most of users have an empty field. If I view this atribute in atribute editor, user have a correct lastlogon stamp.

Anonymous:1.30066E+17 is the number in scientific notation. It's equivalent to 1.30066 * 10^23 or 1.30066 with the decimal point moved 23 places to the right. If you dig around in the formatting menu in excel you can turn that off.Good luck!