How to Convert Active Directory Timestamp to Date in Excel

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 pwd Last Set, account Expires, Last Logon, Last Logon Timestamp, and Last Pwd Set. Each of the attributes has specific uses. For example, Last Logon Timestamp 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.

4 Methods to Convert Active Directory Timestamp to Date in Excel

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 the 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.

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 (7 Easy Ways)

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.

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

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: Converting Date to Number in Formula (2 Ways)

Similar Readings

• Text Won’t Convert to Date in Excel (4 Problems & Solutions)
• How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)
• Convert SAP Timestamp to Date in Excel (4 Ways)
• How to Convert Date to Quarter and Year in Excel
• Remove Timestamps from Date in Excel (4 Easy Ways)

3. Simple Formula for Conversion of Active Directory Timestamp

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

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

Read More: How to Convert Number to Date in Excel (6 Easy Ways)

4. Combined Application of IF and DATE Functions

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

Right away, inset the below formula.

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.

Read More: How to Convert General Format to Date in Excel (7 Methods)

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.

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

• Convert Text to Date and Time in Excel (5 Easy Ways)
• How to Convert Week Number to Date in Excel (2 Suitable Methods)
• Excel VBA to Convert Date and Time to Date Only
• How to Disable Auto Convert to Date in Excel (2 Methods)
• Convert Unix Timestamp to Date in Excel (3 Methods)
• How to Convert 13 Digit Timestamp to Date Time in Excel (3 Ways)
• Convert Text Filter to Date Filter in Excel (3 Examples)