Trying to compare dates in a custom calculated field to current date

[MeganRa3]

Hi all,

Need some help trying to set up a report for my team. Currently we have a calculated field (SD EH Date | Vendor Partnerships) returning a date stamp of the most recent change from another custom field in the form.

I'm trying to compare that date stamp to the current day the report is run ($$TODAY) and determine if the difference in time is within the last 2 weeks, within the last week, or within the last 48hr. Essentially trying to flag changes that happen within a gradient system as we get closer to present day.

I've tried searching these forums and other Experience League resources, was currently attempting the following in the column with no success:

valueexpression=IF(DATEDIFF($$TODAY;{DE:SD EH Date | Vendor Partnerships};days))<=2,"Urgent Change",IF(DATEDIFF($$TODAY;{DE:SD EH Date | Vendor Partnerships};days))<=7,"New Change",IF(DATEDIFF($$TODAY;{DE:SD EH Date | Vendor Partnerships};days)) <=14,"Recent change","No changes")))

Any insight would be appreciated! I'm still a newbie when it comes to learning about Workfront and calculated fields & columns, but am trying my best to get us efficient over here.

[KellieGardner · Accepted solution]

Try this in your value expression. Noting a few things - usining a comma instead of a semicolon and removing the ";days" since you are doing a date diff it's not needed.

IF(DATEDIFF($$TODAY,{plannedCompletionDate})<2,"Urgent Change",IF(DATEDIFF($$TODAY,{plannedCompletionDate})<7,"New Change",IF(DATEDIFF($$TODAY,{plannedCompletionDate})<14,"Recent change","No changes")))

[KellieGardner]

Here are a few links as well that cover documentation on syntax and formatting of data expressions in Workfront.

Overview of calculated data expressions | Adobe Workfront

IF statements overview | Adobe Workfront

Welcome to advanced reporting | Adobe Workfront

[KellieGardner]

@MeganRa3 - you can also still use the >= or <= sign as well - I forgot I removed the = when I was testing for returned values. I recommend only adding it to one of your validation statements and not all.