The 14-digit time format is a legacy representation of Windows FILETIME, encoding the number of 100-nanosecond intervals since January 1, 1601. It appears as YYYYMMDDHHMMSSFF (Year, Month, Day, Hour, Minute, Second, and Hundredths of a second). This structure ensures unique timestamps for file operations and metadata in Microsoft Office and PowerShell environments.
Decoding the Structure
To understand this format, you must break it down into its constituent parts. The string is always exactly fourteen characters long, representing specific components of a datetime object. The first four digits represent the year, followed by two digits for the month, two for the day, two for the hour, two for the minutes, two for the seconds, and finally two digits for hundredths of a second. For example, consider the timestamp2023102514305512. Here is how it parses:
- 2023: Year
- 10: October
- 25: Day
- 14: Hour (2 PM)
- 30: Minutes
- 55: Seconds
- 12: Hundredths of a second (0.12 seconds)
Origin in Windows FILETIME
The primary source of this format is the Windows API functionGetSystemTimeAsFileTime or similar structures used internally by the NTFS file system. Microsoft uses the FILETIME structure to represent dates and times as the number of 100-nanosecond intervals since January 1, 1601. This epoch was chosen because it aligns with the start of the Gregorian calendar reform, providing a consistent baseline for historical date calculations.
When you see this format in PowerShell outputs, XML attributes, or Office document properties, it is often a direct serialization of this 64-bit integer value. While the internal storage is an integer, the textual representation strips the leading zeros from the integer but pads the individual components to ensure fixed-width readability. This padding is why the format remains strictly 14 digits.
Understanding this origin helps explain why certain parsing libraries might fail if they expect a different epoch, such as January 1, 1970 (Unix Epoch) or January 1, 1900 (OLE Automation Date). Converting between these formats requires adjusting for the difference in starting points and the unit of measurement (nanoseconds vs. seconds).
Usage in Microsoft Office and Office 365
Microsoft Office applications frequently use this format when exporting metadata or interacting with older COM interfaces. You might encounter it in Word documents, Excel spreadsheets, or PowerPoint presentations when accessing low-level properties via VBA or external scripts. For instance, the creation date or modification date of a file stored in SharePoint Online may be exposed in this format through certain APIs or logs. In Office 365, while modern APIs like Microsoft Graph prefer ISO 8601 strings (e.g.,2023-10-25T14:30:55Z), legacy connectors or specific audit log exports might still return data in the 14-digit format. This is particularly common when integrating with on-premises Active Directory or when using older versions of the Office Interop assemblies.
If you are working with calendar entries, you may need to parse these values to display them correctly in user interfaces. As discussed in our guide on Resolving Issues with Parsing Exact PowerShell DateTime in Office 365 Calendar Entries, handling these discrepancies is crucial for accurate scheduling and reporting. Ignoring the hundredths component can lead to subtle bugs where events appear out of order or slightly offset in time.
Handling the Format in PowerShell
PowerShell provides robust tools for converting this format back into nativeDateTime objects. Since PowerShell version 3.0, you can often use built-in methods to handle various date formats, but explicit conversion is sometimes necessary for this specific legacy string.
One reliable method involves using the [datetime]::ParseExact static method. You define a custom format string that matches the 14-digit pattern. For example:
$dateString = "2023102514305512"
$format = "yyyyMMddHHmmssff"
$culture = [System.Globalization.CultureInfo]::InvariantCulture
$dateTime = [datetime]::ParseExact($dateString, $format, $culture)DateTime object that you can then manipulate, format, or compare using standard PowerShell cmdlets. The ff specifier in the format string specifically handles the hundredths of a second, ensuring no precision is lost during conversion.
Additionally, if you are dealing with large datasets or performance-critical scripts, manual string manipulation might be faster than reflection-based parsing. You can extract substrings and construct the date manually, though this approach is less readable and more prone to errors if the input format varies slightly.
For those managing tenant templates, understanding these underlying data representations is essential. Errors in template processing, such as those seen in PowerShell: Using Invoke-PnPTenantTemplate with Verbose and Error Handling for Office 365 Tenant Template Transfers, can sometimes stem from mismatched date formats between source and destination environments. Ensuring that all datetime fields are normalized before transfer prevents integrity issues.
Common Pitfalls and Best Practices
A common mistake is assuming the last two digits represent milliseconds. They actually represent hundredths of a second (centiseconds). One hundredth of a second is equal to 10 milliseconds. Therefore, a value of50 in the last two positions means 500 milliseconds, not 50 milliseconds. Confusing these units can lead to significant timing errors in automation scripts.
Another pitfall is timezone awareness. The 14-digit format itself does not contain timezone information. It is typically interpreted as UTC or local time depending on the context of the application generating it. Always verify whether the source system expects UTC or local time before applying conversions. If you are storing these values in a database or sending them over a network, explicitly tagging them with timezone information is highly recommended to avoid ambiguity.
Finally, when validating inputs, always check the length of the string. A malformed timestamp might have fewer digits due to leading zeros being omitted in some contexts, although the standard representation usually pads them. Robust validation logic should reject strings that do not match the expected 14-character length and numeric-only criteria.
Frequently Asked Questions
Why does my Office document show a 14-digit number instead of a date?
This occurs because the document's metadata is exposing the raw Windows FILETIME value rather than a formatted human-readable string. Most modern UIs automatically convert this, but programmatic access or export functions may reveal the underlying integer representation. You can convert it using standard datetime parsing libraries.
Can I convert this format to Unix timestamp directly?
Yes, but you must account for the different epochs. First, parse the 14-digit string into a .NET DateTime object. Then, calculate the difference between that date and January 1, 1970, and divide by 100,000,000 to get seconds. Direct arithmetic without parsing will yield incorrect results due to the 1601 vs 1970 start date difference.
Is the hundredths of a second part always present?
In strict Windows FILETIME serializations, yes. However, some custom implementations might truncate trailing zeros or omit the last two digits for brevity. If you encounter shorter strings, assume the missing digits are zero or validate the input length before parsing to prevent exceptions.
How do I handle timezones with this format?
The format itself is timezone-agnostic. You must determine the timezone context from the surrounding data or documentation. If the source is UTC, treat the parsed DateTime as UTC. If it is local time, convert it accordingly. Always specify the Kind property of the DateTime object to avoid confusion during comparisons.
Does PowerShell have a built-in cmdlet for this?
No single cmdlet exists specifically for "14-digit to DateTime," but [datetime]::ParseExact is the standard and most efficient approach. Avoid using generic Get-Date parsing unless you are certain the input matches one of its supported default formats, which this specific legacy format often does not.