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,"")
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!?
