How To Convert Active Directory Timestamp To Date In Excel (4 ...

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:

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

• 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:

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