Calculated Columns in a Report

[AndrewDa3]

Hey All, Just starting to dip my toes into calculated expressions. Explored some resources on the website but not having much luck. Essentially, we have a project report where I am trying to calculate the number of days between the Project Entry Date and the Target Due Date(A value in our project custom forms). Im trying to get a sense of the typical turnaround time requested by our PMs on certain projects. I found the DATEDIFF function, but I'm not sure how to actually get it to refer to those other fields. I.e. if a project was entered on 1/8 and the Target Due Date was 1/10, it would return 3 Days in the custom column. Is this possible? Andrew Daniels Gartner, Inc.

[Dr_DougWi]

Here is an example calculation to analyze the differences between dates in a reporting column. This text mode is a little elusive to newer text mode users. The idea behind this is to see how many whole days are between the planned and actual completion dates. The rounding gets rid of the odd division that needs to be done on the dates stored as integers. The aggregator calculation is needed for being able to do groupings. Planned Complete to Actual Complete ----------- valueformat=compound aggregator.name=Planned Complete to Actual Complete aggregator.valueexpression=ROUND(IF(ISBLANK({actualCompletionDate}),"",DATEDIFF({actualCompletionDate},{plannedCompletionDate})),1) aggregator.function=AVG aggregator.valueformat=compound linkedname=direct textmode=true name=Planned Complete to Actual Complete valueexpression=ROUND(IF(ISBLANK({actualCompletionDate}),"",DATEDIFF({actualCompletionDate},{plannedCompletionDate})),1) displayname= Considering your application, the calculation changes to accommodate the custom field like so. The text mode above still needs to be used, but tweaked based on the expression below. valueexpression=DATEDIFF({actualCompletionDate},{DE:Target Date}) Have fun with this structure. There are many variations to tinker with on this topic for sure. I used to geek out on these when first mastering text mode 7 years ago. Ping me directly if you need more detail. Doug Williams

[VincentGo]

Hi Andrew, You can also create a calculated field instead of a calculated column in a report. And then add the Calculated Field to your column. Workfront allows the options for DATEDIFF: Calendar days between 2 events WEEKDAYDIFF: Business days between 2 events WORKMINUTESDIFF: Minutes? Format: WEEKDAYDIFF( Date 1 , Date 2 ) For the difference between Entry Date and Planned Completion Date, the formula would look like this. WEEKDAYDIFF( Entry Date , Planned Completion Date ) Vincent Goodwin The Capital Group Companies

[Christina_Jarosz]

I've attempted to do this multiple times but for some reason it simply isn't working. I'm attempting to calculate the difference between a request entry date and a custom date field also on the request, Date of First Use. For some reason it just comes up blank. valueexpression=WEEKDAYDIFF(Entry Date,DE:Date of first use) textmode=true valueformat=HTML displayname=Rush Timing Does anyone have any suggestion? Should this be a project report? Vs. a request report?

[TerryHy]

Try this: valueexpression=WEEKDAYDIFF({entryDate},{DE:Date of first use}) textmode=true valueformat=HTML displayname=Rush Timing Terry Hynd EBSCO Information Services