Create DATEDIFF and WEEKDAYDIFF expressions

Transcript

In this video, you’ll learn what the DATEDIFF and WEEKDAYDIFF expressions calculate and how to create a WEEKDAYDIFF expression in a calculated field. Note, both expressions consider the timestamps for days used in expressions.

Both expressions are used to find the number of days between two dates.

However, one includes all calendar days while the other does not.

The DATEDIFF expression includes the number of calendar days between the due dates. The WEEKDAYDIFF expression only looks at the number of working days as defined by your organization in the Workfront system. For instance, a calendar week is seven days, and a work week typically entails five days, for example, Monday to Friday.

So, when it comes to work and your team wants to know the number of days between an actual start date of a task, meaning when the task changes to the in-progress status, and an actual completion date, when the status was changed to complete, it’s important for the system to know what days to include.

Let’s build a date expression in a calculated field so Workfront can automatically calculate that information for each task your team is working on.

First, consider if you want the calendar dates which include weekends. For this example, use WEEKDAYDIFF to focus on the working days specified by the default schedule in Workfront.

Whichever date expression you use, know that the structure of the expression doesn’t change. Both expressions include the name of the expression and two data points, which are typically a start date and a completion date. Once that’s decided, you start building the expression.

In the custom form, select Calculated in the field options.

Give the field a name, and if needed, fill in the label instructions.

Next, decide what field format to use.

Because this field is focusing on a specific number of days between two dates, the format can be left as text or switch to number, either will work.

As a note, if you’re planning to use this field in a view and to sort the information by this field, number works best.

Click in the Calculation box where you can either start typing in the expression if you already know how to build it, or search for and plug in the needed elements. We’re going to choose the second way. First, search for the expression in the Expressions search box. In this case, WEEKDAYDIFF.

When found, click it to add it to the Calculation box.

At this point, we need to put in our data points, a start date and an end date.

In this example, we want to know the number of days, between when the status moved to in progress and when the status changed to complete. Luckily, the system captures those dates in the actual start date and actual completion date fields.

So, the first data point or field to place in the WEEKDAYDIFF expression is actual start date. Go to the Field search box and type the name of the field, and when it appears in the list, click it to put it in the Calculation box.

The system automatically, starts adding the necessary formatting to make the calculation work in Workfront.

The next data point is the date the task was marked as complete or the actual completion date. Once again, go to the Field search box, type in the name of the field, and click it to put it in the calculation box.

The only additional thing needed is a comma, between the two data points.

For more information on calculation structure, see the “Learn the data expression structure” tutorial.

Once done, save the field and the custom form.

From here, wherever that custom form is attached, the system can start calculating the number of days between the specified dates. And you can use that information in many ways, like a view and a report.

With date expressions like DATEDIFF and WEEKDAYDIFF, you can start to understand how long work is taking, where praise or advice can be given, and if standards are being applied. And ultimately, granting you and your team better communication and collaboration. -