How to Convert Active Directory Timestamp to Date in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

The timestamp of Active Directory is illegible to humans. Fortunately, Excel provides the opportunity of transforming the timestamp to readable dates. In this article, I’ll discuss 4 methods to convert active directory timestamp to date in Excel with proper explanation. So that you can adjust the methods based on your dataset.


What is Active Directory (AD) Timestamp?

Basically, a timestamp is the digital recording system of a specific time for an occurrence. Besides, Active Directory (AD) is system software and domain network prepared by Microsoft. So, the AD timestamp is mainly the advanced system of recording time for every user in the Windows operating system. The timestamp contains 18-digits and stores dates and times with intervals of 100-nanoseconds from 12:00 AM of January 1, 1601. The current AD timestamp is as follows.

132905987010000000 or 1.32905E+17 

Five major attributes of the timestamp are pwdLastSet, accountExpires, LastLogon, LastLogonTimestamp, and LastPwdSet. Each of the attributes has specific uses. For example, LastLogonTimestamp refers to the last login time of a user in a specific server.

However, the 18-digits AD timestamp is not readable for human beings. So we need to convert to legible dates.


How to Convert Active Directory Timestamp to Date in Excel: 4 Methods

In the following dataset, there are 6 AD timestamps. And you need to convert the timestamps to readable dates. Before doing that we’ll get Sequential Serial Numbers in which format Excel stores dates and times. The integer number of the serial numbers represents dates and decimal numbers refer to the time in a fixed date. Therefore, you have to change the format for getting readable dates.

Convert Active Directory Timestamp to Date Excel Dataset


1. Using the IF Logical Function to Convert Active Directory Timestamp to Date in Excel

In the beginning method, I’ll show how you can use the most popular IF logical function to convert active directory timestamp to Excel date. Let’s explore the method in a step-by-step process.

Step 01: Converting Timestamps to Serial Numbers

⇰ To convert the AD timestamp, just insert the following formula and press ENTER.

=IF(B5>0,B5/(8.64*10^11) - 109205,"")

Here, B5 is starting cell of the AD timestamp.

In the above formula, I used the IF function to convert the timestamp if it is greater than zero. Else, it will return a blank.

Using the IF Logical Function to Convert Active Directory Timestamp to Date in Excel

⇰ If you use the Fill Handle Tool to copy the formula for the below cells, you’ll get the following serial number.

Using the IF Logical Function to Convert Active Directory Timestamp to Date in Excel

Step 02: Copy and Paste the Serial Numbers

⇰ Before changing the format you need to copy the serial number numbers by pressing Ctrl + C and select the blank cells.

⇰ Then choose Values (V) from the Paste Options by right-clicking (from the Context Menu).

Convert Active Directory Timestamp to Date Excel Copy and Paste the Serial Numbers

Step 03: Serial Numbers to Dates 

⇰ If you want to get dates, simply click on the drop-down list of the Numbers.

Serial Numbers to Dates

⇰ Alternatively, you can open the Format Cells by using the keyboard shortcut Ctrl + 1. Then pick the 3/14/2012 from the Date category and press OK.

Serial Numbers to Dates

Immediately, you’ll get the following readable dates.

Serial Numbers to Dates

Furthermore, if you want to get the serial numbers in date-time format, look closely at the following screenshot.

Here, you need to select 3/4/12 1:30 PM from the Date category.

Serial Numbers to Dates & Time

When you press OK, you’ll get the following date-time.

Serial Numbers to Dates

Read More: How to Convert Timestamp to Date in Excel


2. Applying Arithmetic Formula to Convert Active Directory Timestamp

Again, you can apply the following formula in Excel to get the serial numbers from the timestamp.

=B5/(60*1440*10000000)-(299*365.24 )+2

Convert Active Directory Timestamp to Date Excel Applying Arithmetic Formula

Also, you can use the TEXT function to get the readable dates instead of using the Format Cells.

=TEXT(C5,"mm/dd/yyyy")

Here, C5 is the starting cell of the serial numbers, and mm/dd/yyyy is the expected format.

Convert Active Directory Timestamp to Date Excel Applying Arithmetic Formula

After using the Fill Handle Tool, you’ll get the following output.

Convert Active Directory Timestamp to Date Excel Applying Arithmetic Formula

Read More: How to Convert Unix Timestamp to Date in Excel


3. Simple Formula for Conversion of Active Directory Timestamp

Luckily, there is another simple formula to convert the timestamps that you can use.

=B5/(8.64*10^11) - 109205

Convert Active Directory Timestamp to Date Excel Simple Formula

Then if you use the methods of converting the serial numbers to date, the readable dates will be like the following.

Convert Active Directory Timestamp to Date Excel Simple Formula


4. Combined Application of IF and DATE Functions

Last but not least, you can apply the combination of IF and DATE functions.

Right away, inset the below formula.

=IF(B5>0, DATE(1601,1,1) +B5 /600000000/1440,””)-693959.8

Here, the DATE function is used to initiate the default initial counting date of the AD timestamp.

Combined Application of IF and DATE Functions to Convert Active Directory Timestamp to Date Excel

After utilizing the methods of converting a serial number to date, you’ll get the following readable dates.

Combined Application of IF and DATE Functions to Convert Active Directory Timestamp to Date Excel


Things to Remember

  • Be careful about the double quotes (“ “) because these may be curly while copying in Excel. Thus you may get  #VALUE! error instead of getting the output.
  • Also, keep in mind that Excel stores date as Sequential Serial Numbers.

Download Practice Workbook


Conclusion

In short, you can use the above 4 methods to convert Active Directory (AD) timestamp to date in Excel. Now choose one based on your preference. According to my observation, the first method generates the most accurate result. Anyway, if you have any queries or suggestions, please share them in the comments section.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo