LDAP Time Converter: How to Read Timestamps

An LDAP time converter translates the large integer timestamps used by Microsoft Active Directory and other LDAP directories into a human-readable date and time, and back again. You enter a timestamp or a date, and the converter gives you the corresponding value in the other format. This page explains the format, how the conversion works, and why you might need it.

Formula

The LDAP timestamp format (often called the “Windows NT time format” or “Active Directory time”) counts the number of 100-nanosecond intervals since January 1, 1601, at 00:00:00 UTC. The formula to convert from a date to an LDAP timestamp is:

LDAP timestamp = (date_value – 25569) × 86400 × 10,000,000

Where date_value is the date expressed as a serial number in Excel’s date system (days since 1899-12-31). The constant 25569 is the number of days between 1601-01-01 and 1899-12-31. The factor 86400 is seconds per day, and 10,000,000 is the number of 100-nanosecond ticks per second.

To convert an LDAP timestamp back to a date:

Date value = (LDAP timestamp / 10,000,000 / 86400) + 25569

The result is a serial date that you can then format as a calendar date and time.

Worked Example

Using the default values from the calculator above, let’s convert the date 2023-01-15 10:30:00 (local time, assuming UTC for simplicity) to an LDAP timestamp.

1. Convert the date to an Excel serial number. For 2023-01-15, the serial number is 44941 (days since 1899-12-31). The time 10:30:00 is 10.5 hours = 0.4375 days, so the full serial value is 44941.4375.
2. Subtract the epoch offset: 44941.4375 – 25569 = 19372.4375.
3. Multiply by seconds per day: 19372.4375 × 86400 = 1,673,778,600 seconds.
4. Multiply by 10,000,000 ticks per second: 1,673,778,600 × 10,000,000 = 16,737,786,000,000,000.

So the LDAP timestamp is 16,737,786,000,000,000 (which is 1.6737786 × 10^16). The calculator’s default timestamp (133500000000000000) corresponds to a date in 2023-04-06, which you can verify by reversing the steps.

When to Use an LDAP Time Converter

You typically need this conversion when working with logs, scripts, or database exports from Windows-based systems. Common situations include:

• Reading the lastLogon, lastLogonTimestamp, or pwdLastSet attributes from Active Directory.
• Parsing audit logs that show LDAP timestamps instead of readable dates.
• Writing scripts (PowerShell, Python, etc.) that need to compare or filter by dates.
• Debugging authentication issues where you need to know when a user last logged on.

If you are not a system administrator, you might encounter LDAP timestamps in exported CSV files from HR systems or security tools. The converter saves you from doing the math manually.

How to Read the Result

When you convert an LDAP timestamp to a date, the result is a local time value. The underlying timestamp is always UTC, but the converter displays it in your system’s time zone. If you need UTC, you can adjust accordingly. For example, if you are in UTC+2 and the converter shows 12:00, the actual UTC time is 10:00.

The date range that LDAP timestamps can represent is enormous: from the year 1601 to the year 30828. In practice, you will only see dates between 2000 and 2100. A timestamp of 0 corresponds to 1601-01-01 00:00:00 UTC. A timestamp of 126227808000000000 corresponds to 2000-01-01 00:00:00 UTC.

Common Mistakes and Misconceptions

One common mistake is confusing LDAP timestamps with Unix timestamps. Unix time counts seconds since 1970-01-01, while LDAP counts 100-nanosecond intervals since 1601. The difference is about 11.6 billion seconds, so you cannot just divide by 10,000,000 and get Unix time.

Another mistake is using the wrong epoch. Some people assume LDAP uses 1970 or 1900. The 1601 epoch is due to the Gregorian calendar cycle used by the Windows file system. Also, be careful with time zones: the LDAP timestamp is always UTC, but the date you enter might be local. Always convert your local time to UTC before converting to a timestamp, or you will be off by the time zone offset.

Finally, note that the 100-nanosecond resolution is far more precise than most applications need. When displaying, you can safely ignore the fractional seconds.

Alternatives to This Converter

If you are comfortable with programming, you can perform the conversion in a few lines of code. In Python, for example:

from datetime import datetime, timedelta
epoch = datetime(1601, 1, 1)
timestamp = 133500000000000000
dt = epoch + timedelta(microseconds=timestamp/10)
print(dt)  # 2023-04-06 13:20:00

In PowerShell, you can use the [DateTime]::FromFileTime() method. Many database systems also have built-in functions. But if you need a quick one-off conversion without writing code, an online converter is the fastest option.

Limitations of the Formula

The formula is straightforward, but it assumes you are using the Gregorian calendar. The LDAP format itself is based on the proleptic Gregorian calendar, which extends the Gregorian system backward before 1582. This is not an issue for practical dates. Also, the formula does not account for leap seconds, which are not part of the LDAP time system. The conversion is exact for any date within the representable range.

Another limitation: if you enter a date before 1601 or after 30828, the result will be negative or overflow. The converter will still produce a number, but it will not be a valid LDAP timestamp.

Frequently Asked Questions

What is the difference between LDAP timestamp and Unix timestamp?

The LDAP timestamp counts 100-nanosecond intervals since 1601-01-01 UTC. Unix timestamp counts seconds since 1970-01-01 UTC. To convert between them, you need to account for the difference in epochs (11644473600 seconds) and the unit difference (1 second = 10,000,000 LDAP ticks).

Why does Active Directory use 1601 as the epoch?

Microsoft chose 1601 because it is the start of the Gregorian calendar cycle that aligns with the 400-year leap year pattern. The Windows file system uses the same epoch, making it consistent across system components.

How do I convert an LDAP timestamp to a readable date in Excel?

You can use the formula =((A1/10000000)/86400)+25569 where A1 contains the LDAP timestamp. Then format the cell as a date. The 25569 is the day difference between 1601 and 1899 (the Excel epoch).

Is the LDAP timestamp in UTC or local time?

The timestamp itself is always UTC. When you convert it to a human-readable date, you need to decide whether to display it in UTC or convert to your local time zone. Most converters show local time by default.

Can I use this converter for FileTime values?

Yes. The Windows FILETIME structure uses the same format: 100-nanosecond intervals since 1601. So any FILETIME value (like file creation times) can be converted with the same formula.

Related

• Convert L/min to m3/hr: Formula & Calculator
• IST to PST Time Converter: How It Works
• BB per 9 Calculator: Formula, Examples & How to Use

in t | LDAP timestamp | - | 133500000000000000 | 0
in d | Date and time (local) | - | 2023-01-15 10:30:00 | 0
out ts | LDAP timestamp | - | (d - 25569) * 86400 * 10000000 | 0
out dt | Date and time (local) | - | (t / 10000000 / 86400) + 25569 | 2