How can I convert LDAP Timestamps in Libre Office to a date time format

Question

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

Answer 1

With the following value in A2

20100407122436Z

I can use the formulae to convert into a date time object that Libre 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 Libre 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.