Convert Active Directory Timestamp

Automation Master
Automation Master
7 2 7,144

Sometimes, you have a need to pull back a date from Active Directory to use in your workflow. Maybe you're querying for Account Expiration dates, or maybe you want to know when that employee last logged in. And maybe you need to store that in a column for some reason.

If that's the case, then you'll be met with a bit of grief. 

Active Directory has decided, for whatever reason, that 100-nanoseconds is the super cool way to store a date/time.  And SharePoint does not care for that. It would much prefer that you give it a format it understands, if you wish to view it as a date.

But, using the action Query Excel Services in our workflow, we can get a readable date. 

It's super simple -- the workflow, not the math -- (and I'm a workflow person not a math person) -- so let's dive right in.

The formula in Excel to convert the returned Active Directory time into a Date looks like this: 

(Where A1 = the cell containing your timestamp)

=IF(A1>0,A1/(8.64*10^11) - 109205,"")

Source: TechNet

1) Enter the formula above into cell B1 in a new Excel sheet, then, format cell B1 as a Date/Time cell. Save the Excel file into your SiteAssets library (or another library!)

2) Open the workflow where you wish to use the converted time, then query LDAP, and put your returned timestamp into a text variable.

3) Set up your Query Excel Services action like this: 

  • The workbook path should be the full, direct URL to the workbook
  • Ensure the "Retrieve as formatted text" is selected (or else you'll get it as Excel's timestamp)
  • Your variable can be a Single Line of Text

4) Use the Convert Value action to convert from text to date.

Bada boom, all done, and we didn't even have to TOUCH the math! Which is ideal, right!?

Not applicable

Hi Rhia - thanks for sharing such great stuff! I know you're building some awesome solutions with Nintex technologies. I recommend sharing the stories to nominate for the Solution Innovation Awards. Winning customers and partners of the solution will get waived registration to the Nintex xchange conference along with promotion on the Nintex corporate channels. 

Happy to provide you with more info or help cater your nomination to what the judges are looking for!! Just reach out! Thanks!

Automation Master
Automation Master

Oh we're definitely going to submit - just need to figure out which solution to submit