Excel
Excel stuff!
Disabled or enabled:
=IF(LEFT(RIGHT(DEC2BIN(MOD(G4;8);8);2);1)="1";"disabled";"enabled")
Password never expire
=IF(RIGHT(DEC2BIN(MOD([@userAccountControl]/65536;256);8);1)="1";"neverexpire";"expire")
Converting LDAP dates
DATES that look like 127524839567199000 (pwdLastSet and accountExpires)
These values store time values in 100 nanoseconds segments from 01-Jan-1601. As we will use Excel, it is useful to know that Excel stores dates and times as a number representing the number of days from 01-Jan-1900, plus a fractional portion of a 24 hour day; something like ddddd.ttttt (serial date and times). To convert these LDAP date values to meaningful dates, use the following code:
Converting LDAP date values to human readable dates in Excel; F2 is source cell
- =IF(F2>0,F2/864000000000 - 109205,"")
- =IF(F2>0;F2/864000000000 - 109205;"")
NOTE: accountExpires also can hold values of 0 or 9223372036854770000 which basically mean that these accounts are NEVER set to expire.
However, these values return an error on the serial date format in Excel.
So, a better way to do this in Excel is shown using the following code:
Converting LDAP date values to human readable dates in Excel; F2 is source cell
- =IF(OR(F2=9223372036854770000,F2=0)=TRUE,73050,IF(F2>0,F2/864000000000 - 109205,""))
- =IF(OR(F2=9223372036854770000;F2=0)=TRUE;73050;IF(F2>0;F2/864000000000 - 109205;""))
DATES that look like 20050210223453.0Z (whenChanged)
This value representation is typical for attributes like whenChanged. To convert these LDAP date values to meaningful dates, use the following code:
Converting LDAP date values to human readable dates in Excel; F2 is source cell
- =DATEVALUE(CONCATENATE(RIGHT(LEFT(F2,8),2),"-",RIGHT(LEFT(F2,6),2),"-",LEFT(F2,4)))
- =DATEVALUE(CONCATENATE(RIGHT(LEFT(F2;8);2);"-";RIGHT(LEFT(F2;6);2);"-";LEFT(F2;4)))