Help with complex valueexpression for late tasks

[Jcosta16]

In a project collections report, I'm trying to work through this text mode to show:

If milestone = Complete, CONCAT "Completed, actualCompletionDate" (that part is working)

If milestone isn't complete, CONCAT "Due, plannedCompletionDate" and if the planned completion date is past $$TODAY, include [RED DOT]  . If planned completion date < $$TODAY, include 🟢.

This value expression is successfully showing tasks that are complete or that are not complete but not overdue: valueexpression=IF({milestone}.{name}="STRATEGY APPROVED",IF(AND({status}="DED", {plannedCompletionDate}<$$TODAY), " [RED DOT] N/A",IF(AND({status}="DED", {plannedCompletionDate}>$$TODAY), "🟢 N/A",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY, CONCAT(" [RED DOT] ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

Any ideas on where I'm going wrong that I'm not pulling in tasks that are NOT complete and ARE overdue (and including that [RED DOT])? FYI that for some reason the red dot isn't populating here so i've put RED DOT in its place.

[KristenS_WF]

Instead of using {plannedCompletionDate}<$$TODAY, maybe try something like DATEDIFF({plannedCompletionDate}, $$TODAY)<0?

You might try testing it in a smaller valueexpression to see if it gives you what you expect before introducing it into your larger valueexpression.

[Jcosta16 · Accepted solution]

I figured it out! This will pull in the milestone planned completion date if it's not complete and do a green dot if it's not overdue and a red dot if it is. If it's complete, it'll pull in Completed and then the actual completion date!

valueexpression=IF({milestone}.{name}="AUDITS RECEIVED",IF(AND({status}="DED","N/A", {plannedCompletionDate}<$$TODAY), " ",IF(AND({status}="DED","N/A", {plannedCompletionDate}>$$TODAY), "🟢 ",IF(CONTAINS("CPL",{status}), CONCAT("Completed ",{actualCompletionDate}),IF({plannedCompletionDate}<$$TODAY, CONCAT(" ",{plannedCompletionDate}),CONCAT("🟢 ",{plannedCompletionDate}))))))

[skyehansen]

I usually go to the calculated data expressions page to get all my functions and syntax. I'm not seeing anything for your "AND" command, so can you walk me through what it's supposed to do, or provide a link so I can read up on it?

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/...

[Jcosta16]

I'm not actually using any calculated fields ... but now I might look into it to help me out in the future!