Working with dates in the Workflow Conductor Calculate Widget

With the release of Workflow Conductor 1.5, the Calculate Widget added support for Excel-style string, mathematical, and date calculations. A link to the list of functions, syntax, and examples is available in the Widget by clicking the  icon in the Widget formula box. 

This article provides specific information about date formats in the Calculate widget.

Dates in Excel are represented in a serial number format, as the number of days since January 1, 1900. For example, the serial number representation for October 5, 2010, is 40431. Excel often automatically displays serial numbers in a standard date format using cell formatting, so serial numbers are usually invisible to the Excel user. 

The Workflow Conductor Calculate Widget uses the same serial number representation as Excel, but SharePoint does not automatically recognize serial numbers as dates. If you use a Calculate widget to calculate a date and you want to use that date in SharePoint, use the TEXT function to convert the result of your calculation from a serial number to a string that matches the SharePoint date display format. For example:

DATEVALUE({Reference:Current Date})+10 adds 10 days to the Current Date Easy Reference lookup. If Current Date is October 5, 2010, the result will be 40441(40431+10). SharePoint does not understand 40441 as a date. To convert it to a date, surround the DATEVALUE function with a TEXT function, like this:

TEXT(DATEVALUE({Reference:Current Date})+10,"m/d/yyyy")

The new result is 10/15/2010. After the serial number is converted to a date string format that SharePoint understands, you can:

• Write the date to a SharePoint Date/Time column with the Update Item, Set Field Value, or Create Item Widgets
• Use the calculated date to set a task Due Date in the Create Task, Request Feedback, or Request Approval Widgets
• Compare the calculated date with a date stored in a SharePoint using lookups or Widgets that have condition properties