Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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

share|improve this question
up vote 1 down vote accepted

With the following value in A2


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


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


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, – nelaar Jul 2 '12 at 8:12

Your Answer


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.