Wisam Fawzi

Convert date/time format to Active Directory timestamp

Blog Post created by Wisam Fawzi on Aug 14, 2015

I recently had a request to get the AD user accounts expiring in 30 days from today's date, using Nintex Workflow.

 

The Background:

An AD timestamp (AKA LDAP time, WinTime or FileTime) is the number of 100-nanoseconds since the year 01/01/1601 @ 00:00.

 

There's a number of online tools that help us calculating this. For example,

    • To get the number of 100-nanoseconds between the years 01/01/1601 @ 00:00 and 01/01/2016 @ 00:00,

                     we can use silisoftware online free tool

                    So, 130960980000000000 is the number of 100-nanoseconds between 01/01/1601 @ 00:00 and 01/01/2016 @ 00:00, and it's also the AD timestamp for 01/01/2016 @ 00:00

 

The Challenges:

  1. Convert date/time format to AD timestamp.
  2. Convert the AD timestamp produced from the calculations, to a text readable by AD.

 

The Solution:

  1. To convert date/time format to AD timestamp:

Nintex allows us to get the number of seconds since 01/01/1900 @ 00:00, so we will have to do 3 steps to get the number of 100-nanoseconds since 01/01/1601 @ 00:00:

  1. Get the number of seconds since 01/01/1900 @ 00:00, save in varNum_secsSinceJanFirst1900.
    1. In a "Set variable" action:

Set:

YourNumberVariable (varNum_secsSinceJanFirst1900)

 

 

 

 

Equals:

Workflow Data

YourDateTimeVariable

(varDate_TodaysDate)

Number of Seconds since 00:00:00 1/1/1900

 

 

2. Multiply var_secsSinceJanFirst1900 by 10^7 (100-nanosecs), save result in var_100nanoSecsSinceJanFirst1900

    • So, varNum_100nanoSecsSinceJanFirst1900 = varNum_secsSinceJanFirst1900 * 10^7

 

3. Add the number of 100-nanoseconds between 01/01/1601 @ 00:00 and 01/01/1900 @ 00:00, to varNum_100nanoSecsSinceJanFirst1900, save result in varNum_requiredDateinADTimeStampFormat.

              • The number of 100-nanosecs between 01/01/1601 @ 00:00 and 01/01/1900 @ 00:00, taking into consideration the leap years is: 94354848000000000, you'll have to trust me on this.

 

The calculations combined are as follows:

varNum_requiredDateinADTimeStampFormat = (varNum_secsSinceJanFirst1900 * 10^7) + 94354848000000000

 

 

2. To convert the AD timestamp produced from the calculations, to a text readable by AD:

The result of the calculations done above, is something like this: 1.3086031037335E+17, if you pass the number in this format to AD, AD won't recognize the number as an AD timestamp, and you will get zero result.

An AD timestamp is an 18 digit number, so we need to convert 1.3086031037335E+17 to 130860310373350000. We can do this by following these steps:

  1. Convert the varNum_requiredDateinADTimeStampFormat number variable to text, save in varText_requiredDateinADTimeStampFormat.
  2. Apply the following inline functions to varText_requiredDateinADTimeStampFormat:
    1. Remove the dot ('.'):
      1. fn-Replace({varText_requiredDateinADTimeStampFormat},.,)
      2. Store in the same field: varText_requiredDateinADTimeStampFormat
    1. Remove 'E+17':
      1. fn-Replace(varText_requiredDateinADTimeStampFormat,E+17,)
      2. Store in the same field: varText_requiredDateinADTimeStampFormat
    2. Add zeroes to complete 18 digits:
      1. fn-PadRight(varText_requiredDateinADTimeStampFormat,18,0)
      2. Store result in the same field: varText_requiredDateinADTimeStampFormat

 

 

This how it looks combined:

Replace WorkflowVariable:sl_12hrsBeforeADTimeStamp in the below screenshot with WorkflowVariable:varText_requiredDateinADTimeStampFormat

 

The result of the above string operations should be something like this: 130860310373350000, when you pass this to AD, you should get the expected results.

 

The whole workflow is as follows (attached):

Outcomes