Text Mode Report: IF Statement in column, to catch us not meeting promised dates

[CathyKe1]

I am working with a task report, and attempting to add a column using text mode to show if the task is on time or late, based on "Date Needed." This s a date entered into a field on the project custom form.

I’ve struggled to get it to work, so I backed off to get a more straight forward valueexpression to work.

This “Task Late?” column works

displayname=Task Late?

linkedname=direct

namekey=plannedStartDate

querysort=plannedStartDate

textmode=true

valueexpression=IF({plannedCompletionDate}<{projectedCompletionDate},"Late","On Time")

valueformat=HTML

Note: The caveat to the above is that it seems to show Late or On Time correctly for the most part, but there are a few that show “late” even though the dates are the same. I think it might be considering the time. How can I tell it to ignore the time, and only consider the date?

What I really want to use though, is the Date Needed field from a custom form, compared to the task planned completion date.

This is what I’ve tried, but every item is displaying On Time.

displayname=Proj Late?

linkedname=project

namekey=view.relatedcolumn

namekeyargkey.0=project

namekeyargkey.1=Date Needed

querysort=DE:project:Date Needed

textmode=true

valueexpression=IF({DE:project:Date Needed}<{plannedCompletionDate},"Late","On Time")

valueformat=HTML

Ideally, I’d like to Filter the entire report to only show the projects where the Date Needed (project) is less than the task planned completion date – so it would show me the projects that are currently running behind the date it is needed. I don’t think that’s possible, though?

[CathyKe1 · Accepted solution]

I am replying to my own post, with an update in case it may help anyone else. And, I have one outstanding question.

I figured out the valuexpression I needed in order to show if the project is Late or On Time using the field from a custom form:

valueexpression=IF({project}.{DE:project:Date Needed}<{plannedCompletionDate},"LATE","On Time")

I also realized I can accomplish this in another way by comparing the fields and using conditional formatting to highlight the late projects. I like this approach better. However, either way I still have the issue of some projects showing as Late, even though the Date Needed is the same as the task Planned Completion Date.

I think I have to utilize CLEARTIME, but can't figure out the syntax to use it. Can anyone help with that, please?

• The valueexpression used in the "Proj Late?" field is above for reference
• Or, the text mode with the conditional formatting to compare the fields is below

displayname=Project Due Date
linkedname=project
namekey=view.relatedcolumn
namekeyargkey.0=project
namekeyargkey.1=Date Needed
querysort=DE:project:Date Needed
row.0.styledef.applyallcases=true
row.0.styledef.case.0.comparison.icon=false
row.0.styledef.case.0.comparison.isrowcase=true
row.0.styledef.case.0.comparison.leftmethod=DE:project:Date Needed
row.0.styledef.case.0.comparison.lefttext=DE:project:Date Needed
row.0.styledef.case.0.comparison.operator=lt
row.0.styledef.case.0.comparison.operatortype=date
row.0.styledef.case.0.comparison.rightmethod=plannedCompletionDate
row.0.styledef.case.0.comparison.righttext=plannedCompletionDate
row.0.styledef.case.0.comparison.trueproperty.0.name=bgcolor
row.0.styledef.case.0.comparison.trueproperty.0.value=eac6c9
row.0.styledef.case.0.comparison.truetext=
sortOrder=1
sortType=asc
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.isrowcase=true
styledef.case.0.comparison.leftmethod=DE:project:Date Needed
styledef.case.0.comparison.lefttext=DE:project:Date Needed
styledef.case.0.comparison.operator=lt
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=bgcolor
styledef.case.0.comparison.trueproperty.0.value=eac6c9
styledef.case.0.comparison.truetext=
textmode=true
valuefield=project:Date Needed
valueformat=customDateAsString

Thank you!