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.

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:

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

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

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.

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.

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

• Immediately, you’ll get readable 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.

When you press OK, you’ll get the following values.

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:

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

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

After dragging down the Fill Handle, you’ll get the following output.

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:

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

Method 4 – Combining IF and DATE Functions

Here’s another formula you can use:

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

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

Related Articles

• How to Convert Week Number to Date in Excel
• How to Convert SAP Timestamp to Date in Excel
• How to Convert 13 Digit Timestamp to Date Time in Excel

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