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.
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.
⇰ If you use the Fill Handle Tool to copy the formula for the below cells, you’ll get the following serial number.
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).
Step 03: Serial Numbers to Dates
⇰ If you want to get dates, simply click on the drop-down list of the Numbers.
⇰ 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.
Immediately, you’ll get the following readable 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.
When you press OK, you’ll get the following date-time.
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
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.
After using the Fill Handle Tool, you’ll get the following output.
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
Then if you use the methods of converting the serial numbers to date, the readable dates will be like the following.
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.
After utilizing the methods of converting a serial number to date, you’ll get the following readable dates.
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.
