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 *


*