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

 

What Is an Active Directory (AD) Timestamp?

A timestamp is the digital recording system of a specific time for an occurrence, and the Active Directory (AD) is system software and domain network in Microsoft systems. The AD timestamp contains multiple useful instances and fields of recorded 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. For example, the AD timestamp for midnight on Jan 1, 2024, is:

133485408000000000 or 1.33485408E+17

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

However, the 18-digit timestamp is not particularly useful for everyday use and filing, which is why it needs to be converted into proper dates.


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

Consider a dataset, there are 6 AD timestamps which need to be converted to readable dates. To do that, the example will use 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


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

Step 1: Converting Timestamps to Serial Numbers

  • To convert the AD timestamp, just insert the following formula in B5 and press Enter:

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

The IF function converts the timestamp if it is greater than zero. Otherwise, it will return a blank.

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

  • Drag the Fill Handle Tool down to copy the formula to other cells in column C.

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

Step 02: Copy and Paste the Serial Numbers

  • Copy the serial number numbers by pressing Ctrl + C.
  • Select the blank cells to the right.
  • Right-click, then select the Values option (123 icon) from Paste Options.

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

Step 03: Serial Numbers to Dates 

  • Click on the drop-down list of the Numbers category in the Home tab. Alternatively, you can open Format Cells by using the keyboard shortcut Ctrl + 1.

Serial Numbers to Dates

  •  Go to the Date category, choose any format you want, and press OK.

Serial Numbers to Dates

  • Immediately, you’ll get readable dates.

Serial Numbers to Dates

If you want to get the serial numbers in date-time format, 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 values.

Serial Numbers to Dates

Read More: How to Convert Timestamp to Date in Excel


Method 2 – Applying Arithmetic Formula to Convert Active Directory Timestamp

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 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 dragging down the Fill Handle, 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


Method 3 – Simple Formula for Conversion of Active Directory Timestamp

Here’s another formula you can use:

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

Convert Active Directory Timestamp to Date Excel Simple Formula

You can use any of the earlier methods for converting the serial numbers to date to get the final result.

Convert Active Directory Timestamp to Date Excel Simple Formula


Method 4 – Combining IF and DATE Functions

Here’s another formula you can use:

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

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

Convert the numbers into dates in any way you want (see methods above), and you’ll get 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


Related Articles


<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo