Issue with Calculated Custom Field Format

[AndrewBo]

Hello, I was trying to create a 'past due' indicator field when issues are past due for reporting purposes. I created the field and it works fine but it was bugging me that closed issues would still show the 'past due' status. I wanted to have that go away once an issue has a status of closed. I put together the statement below following the formatting on Workfront's site but it just won't work. Even when the issue has been closed, it still displays as 'PAST DUE'. If anyone has any ideas, let me know. IF(Status!="Closed"&&Planned Completion Date<$$TODAY,"PAST DUE","") Workfront actually showed the example below on their help page for condition operators in calculated custom expressions. I thought felt like I was on the right track but apparently not. And && Use this modifier to indicate that the condition is fulfilled when the expression finds an item that fulfills two conditions at the same time. For example, use the following statement in a calculated custom field to build an "IF" statement that finds projects that are in Current status and have a Condition of At Risk and marks them as "Mediation Needed." IF(Status="CUR"&&Condition="AR","Mediation Needed","") Thanks,
Andy Bolazina Data and Analytics Workforce Solutions Equifax Inc. O 314-214-7288 C 314-497-7368 "mailto:andy.bolazina@equifax.com" andy.bolazina@equifax.com This message contains proprietary information from Equifax which may be confidential. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e-mail "mailto:postmaster@equifax.com" postmaster@equifax.com . Equifax® is a registered trademark of Equifax Inc. All rights reserved.

[NRYNR]

Hi Andy, Are you calculating this value in the report's view or on a calculated field on the custom form? The recommendations from the community will vary slightly, depending on what method you're using. I hope it's on the view because calculated fields require an edit on the issue to update the "Past Due" value, which won't help you when you need to know when issues are late in real time. If you calculate it in the view, the value is as fresh as the page load. Thanks, Narayan

[NRYNR]

Hi Andy, I'm glad you now understand that calculated fields on custom forms aren't dynamic. In fact, if you bulk edit 2 or more issues, tasks, or projects from a list and scroll all the way to the end of the screen, you'll find a "Recalculate Custom Expressions" checkbox, which can be used to force the calculations to update. All other edits to the records will also trigger an update. However, when attempting to track if things are "Past Due", you don't want to have to bulk edit and recalculate every time get the latest. So, I suggest you use a valueexpression in the view to determine if the task is "Past Due". Here's an example where we add a column to the view... IF Actual Completion Date is blank (the task is still open) AND Planned Completion Date < NOW (past due), render "Past Due", otherwise render "On Time" valueformat=HTML valueexpression=IF(ISBLANK({actualCompletionDate})&&{plannedCompletionDate}<$$NOW,"Past Due","On Time") displayname=Past Due/On Time Please let us know how this works out. Thanks, Narayan