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.

[#2]

@jon_chen

I'm looking looking to add conditional formatting to a view that would highlight a cell when the

date doesn't match the date of custom date field called Deliverable Distribution Date.

I've followed your steps, but I'm looking to use a modifier of 'Not Equal', and can't seem to make this work.

Any ideas?

• valuefield=dueDate
  styledef.case.0.comparison.operatortype=date
  styledef.case.0.comparison.trueproperty.0.value=bold
  styledef.case.0.comparison.trueproperty.0.name=fontstyle
  styledef.case.0.comparison.trueproperty.1.value=d30519
  styledef.case.0.comparison.trueproperty.1.name=textcolor
  styledef.case.0.comparison.trueproperty.2.name=bgcolor
  styledef.case.0.comparison.trueproperty.2.value=eac6c9
  styledef.case.0.comparison.icon=false
  styledef.case.0.comparison.operator=ne
  styledef.case.0.comparison.righttext=FIELD:Deliverable Distribution Date
  styledef.case.0.comparison.rightmethod=Deliverable Distribution Date
  styledef.case.0.comparison.lefttext=dueDate
  styledef.case.0.comparison.leftmethod=dueDate
  styledef.case.0.comparison.truetext=
  linkedname=direct
  valueformat=atDate
  namekey=dueDate
  textmode=true

[#3]

• valuefield=dueDate
  styledef.case.0.comparison.operatortype=date
  styledef.case.0.comparison.trueproperty.0.value=bold
  styledef.case.0.comparison.trueproperty.0.name=fontstyle
  styledef.case.0.comparison.trueproperty.1.value=d30519
  styledef.case.0.comparison.trueproperty.1.name=textcolor
  styledef.case.0.comparison.trueproperty.2.name=bgcolor
  styledef.case.0.comparison.trueproperty.2.value=eac6c9
  styledef.case.0.comparison.icon=false
  styledef.case.0.comparison.operator=ne
  styledef.case.0.comparison.righttext=FIELD:DE:Deliverable Distribution Date
  styledef.case.0.comparison.rightmethod=DE:Deliverable Distribution Date
  styledef.case.0.comparison.lefttext=dueDate
  styledef.case.0.comparison.leftmethod=dueDate
  styledef.case.0.comparison.truetext=
  linkedname=direct
  valueformat=atDate
  namekey=dueDate
  textmode=true

[#4]

@jon_chen

 Thank you for providing this tutorial. Is it possible to have 2 conditions within a column. For example if the actual completion date is later than the planned completion date it should be red, and if the actual completion date is earlier that the planned completion date, it should be in green. I can only do one or the other. I cannot combine both in a column. Any suggestions

Thanks 

[#5]

can you use two separate rules? I would be interested in two conditions in one rule - like status = new and planned completion is before today

[#6]

Hi @RhondaGTriggs , 

I figured it out. If you want to have more than one condition in a text mode then you include them with numbers. See below what I came up with:

displayname=Act Comp
linkedname=direct
namekey=actualCompletionDate
querysort=actualCompletionDate
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=actualCompletionDate
styledef.case.0.comparison.lefttext=actualCompletionDate
styledef.case.0.comparison.operator=gt
styledef.case.0.comparison.operatortype=date
styledef.case.0.comparison.rightmethod=plannedCompletionDate
styledef.case.0.comparison.righttext=plannedCompletionDate
styledef.case.0.comparison.trueproperty.0.name=fontstyle
styledef.case.0.comparison.trueproperty.0.value=bold
styledef.case.0.comparison.trueproperty.1.name=textcolor
styledef.case.0.comparison.trueproperty.1.value=8B0000
styledef.case.0.comparison.truetext=
styledef.case.1.comparison.icon=false
styledef.case.1.comparison.leftmethod=actualCompletionDate
styledef.case.1.comparison.lefttext=actualCompletionDate
styledef.case.1.comparison.operator=lt
styledef.case.1.comparison.operatortype=date
styledef.case.1.comparison.rightmethod=plannedCompletionDate
styledef.case.1.comparison.righttext=plannedCompletionDate
styledef.case.1.comparison.trueproperty.0.name=fontstyle
styledef.case.1.comparison.trueproperty.0.value=bold
styledef.case.1.comparison.trueproperty.1.name=textcolor
styledef.case.1.comparison.trueproperty.1.value=008000
styledef.case.1.comparison.truetext=
textmode=true
valuefield=actualCompletionDate
valueformat=atDate

You can try it out and see if it works 

[#7]

Hello,
I want to be able to color the cell red if the "project actual start date" is greater than the "baseline planned start date" AND color it green if the "project actual start date" it less than or equal to the "baseline planned start date". 

With the following code, red appears but not green. What am I doing wrong?

displayname=Start Date Variance 2
linkedname=direct
namekey=project:actualStartDate
namekeyargkey.0=project
namekeyargkey.1=project:actualStartDate
querysort=project:actualStartDate
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=project:actualStartDate
styledef.case.0.comparison.lefttext=project:actualStartDate
styledef.case.0.comparison.operator=gt
styledef.case.0.comparison.operatortype=date
styledef.case.0.comparison.rightmethod=plannedStartDate
styledef.case.0.comparison.righttext=plannedStartDate
styledef.case.0.comparison.trueproperty.0.name=textcolor
styledef.case.0.comparison.trueproperty.0.value=000000
styledef.case.0.comparison.trueproperty.1.name=bgcolor
styledef.case.0.comparison.trueproperty.1.value=eac6c9
styledef.case.0.comparison.truetext=
styledef.case.1.comparison.icon=false
styledef.case.1.comparison.leftmethod=project:actualStartDate
styledef.case.1.comparison.lefttext=project:actualStartDate
styledef.case.1.comparison.operator=lt
styledef.case.1.comparison.operatortype=date
styledef.case.1.comparison.righttext=plannedStartDate
styledef.case.1.comparison.trueproperty.0.name=textcolor
styledef.case.1.comparison.trueproperty.0.value=f00
styledef.case.1.comparison.truetext=
textmode=true
valueexpression=IF(CLEARTIME({project}.{actualStartDate})<CLEARTIME({plannedStartDate}),CONCAT(WEEKDAYDIFF(CLEARTIME({plannedStartDate}),CLEARTIME({project}.{actualStartDate}))," Days Earlier",""),IF(CLEARTIME({project}.{actualStartDate})>CLEARTIME({plannedStartDate}),CONCAT(WEEKDAYDIFF(CLEARTIME({plannedStartDate}),CLEARTIME({project}.{actualStartDate}))," Days Later",""),IF(CLEARTIME({project}.{actualStartDate})=CLEARTIME({plannedStartDate}),"On Time","")))
valuefield=project:actualStartDate
valueformat=HTML
width=100

[#8]

@jon_chen Thank you for this text mode tip! I have been trying to compare two custom date columns with no luck. I was missing the last step of going back into text mode and adding the line styledef.case.0.comparison.rightmethod=.