Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If I query the directory, I get the following results.

ldapsearch2.4 -x -b "dc=localhost,dc=com" "cn=auser" "+" | grep stamp
createTimestamp: 20100407122221Z
modifyTimestamp: 20100407122221Z
createTimestamp: 20100407122436Z
modifyTimestamp: 20111229151358Z

I am pulling the directory into an spread sheet so we can perform an audit on our list of users. The current date stamp is not simple to convert into a spread sheet understandable date time object.

This helped me understand how the actual date is stored but I still need a way to convert it. http://www.novell.com/coolsolutions/qna/6668.html

This will return Greenwich Mean Time (GMT) in the following format: yyyymmddhhmmssZ

share|improve this question

1 Answer 1

up vote 1 down vote accepted

With the following value in A2

20100407122436Z

I can use the formulae to convert into a date time object that Libra Office understands.

=DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2)) + TIME(MID(A2,9,2),MID(A2,11,2),MID(A2,13,2))

Which is stored as Libra office epoch floating point number

40275.5170833333

With date formatting on the result It shows up as

2010/04/07 12:24:36

I am not using the time zone information as I don't have users accross different time zones in the directory. You can pull the time zone using the following formulae

=RIGHT(A2,1)

Now I can sort by date all the user information I pulled from LDAP.

share|improve this answer
    
Excellent. Ensure that the conversion cell supports the entire generalized time syntax as defined in rfc4517. –  Terry Gardner Jun 30 '12 at 9:32
    
@TerryGardner, the time syntax can be set by the format options proved by office. Libra Office simply stores it a long int, help.libreoffice.org/Common/Number_Format_Codes. –  nelaar Jul 2 '12 at 8:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.