Comparing dates (and more!) with conditional formatting.

[#1]

Did you know that you can use conditional formatting on a View to compare two date fields?

Before you start, just a bit of review. Conditional formatting allows you to customize how information displays on a report. You can make information stand out by making the text bold or highlighting a field. The formatting is set up as part of the View. (For more information, see Using conditional formatting in Views.)

You can use conditional formatting for a variety of things—for example, making a task appear in red when the progress status is late or highlighting in gray all of the project planned completion dates that fall in the current quarter.

Let’s look at an example where we use conditional formatting to highlight when the projected completion date on a task is later than the planned completion date. The formatting lets you manually compare dates and shows you, at a glance, how work is progressing.

Just a warning...Setting this up will take a bit of back-and-forth between several windows … and a little bit of text mode reporting. Just follow the instructions and it’ll work, even if you don’t know text mode. (You can learn more about text mode reporting in Workfront’s Advanced Reporting courses, available in Workfront Ascent.)

Start with a View that contains the planned completion date and select the column.

Then click Advanced Options in the upper-right corner. Click Add Column Rule.

Set the criteria for the rule at the top of the window. In this example, leave Task>Planned Completion Date. 

Select Less Than from the qualifier drop-down menu. And then just select today’s date. This is placeholder information until we complete a few other steps.

Add the text formatting—bold text with a yellow background. Or whatever type of formatting works best for you.

Don’t forget to click Add Rule to save what you just set up. This takes you back to the Advanced Options page.

Now click Switch to Text Mode in the upper-right corner. Click into the text mode window to active the editing.

Look for where it says styledef.case.0.comparison.rightmethod=plannedCompletionDate. You can see that it lists today’s date.

Delete the date and replace it with FIELD:ID.

Hit Save, and then click Switch to Standard Mode.

Now you need to go back into the Advanced Options and make an edit to the conditional formatting rule you added previously.

In the value box, instead of today’s date, you’ll see it now lists Task>ID. When you click into the field, you see a variety of field source and field name options.

Because we want to compare the projected completion date with the planned completion date, we’ll pick Task > Projected Completion Date from the menu.

What this criterion says, is that any time the planned completion date is less than—or earlier than—the projected completion date, highlight the field.

Click the Done button, and then—one more time— switch back to the text mode window.

Find the line that says styledef.case.0.comparison.righttext=FIELD:projectedCompletionDate.

Under that line add a line—styledef.case.0.comparison.rightmethod=projectedCompletionDate. You can copy the text from the line above, but make sure not to copy FIELD:. This is the name of the field the comparison is using.

Click all of the save buttons to get out of the View editing mode, and let’s take a look at your report.

Now you see the planned completion date field has bold text and is highlighted in yellow. This means that, for these tasks, the planned completion date is earlier than the projected completion date. (In the image below, we added the project completion date column so you could see the comparison between planned and projected dates.)

What does this mean? It means that Workfront is estimating, based on work already done and in progress, that the tasks will be completed after the completion date set by the project plan.

You can do this same thing but substituting in different date type fields—planned completion/start date, projected completion/start date, or estimated completion/start date. For more about the different date types in Workfront, see Definitions for the project, task, and issue dates within Workfront.

For another example, see Comparing fields in conditional formatting.