Converting from AD time to Excel time

Here's a little formula that works to convert times and dates from Active Directory (or other LDAP servers) to Excel – really useful to use if you've exported a number of entries from Active Directory to an Excel spreadsheet or CSV, and want to see them as dates:

=(B1-94353120004495000)/864000000000

Clearly, 864000000000 refers to the number of 100-nanosecond intervals in a day. It's possible that the offset value of 94353120004495000 is not going to be correct for your environment, so don't forget to test this – time zones may affect the accuracy of this value.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>