Custom Column Reporting - Calculated Fields

[KatelynnKe2]

Hello!

I am having trouble with a DATEDIFF calculated column on a task report. One column is a calculated date field on a custom form field on the task level (Go Live Date Task Level) and the other is the actual completion date of the task (Actual Completion Date). When editing in text mode, none of my attempts produce a successful result. All results are blank, is what I am trying to accomplish possible? 

[Sven-iX]

Hi @KatelynnKe2 

The calculated field needs to be of Date or Date/Time type - sadly this can only be set when you first create the field. 

If the calc field is defined as Text, I get weird results, because here (EMEA) the calc field shows the day coming before month. 
And when WF converts this to a date it does it wrong

[KatelynnKe2]

@Sven-iX thank you for your response, my calculated field is set up as a date field. I’m unsure why the DIFF column returns no value. 

[Sven-iX]

can you print out the 2 fields you're trying to compare like i did in the screenshot? 
and share your calculation. 
Hard to diagnose blindly

[KatelynnKe2]

Please see the below. Comparing the task level calculated date custom field to the actual completion date of the task. 

[KellieGardner]

Not sure what your text mode formula is or your data in the columns you are calculating, but I would check there is a difference in the days you are pulling into your calculation?

Is Go Live Date Task Level different than Actual Completion Date?

DATEDIFF

Returns the number of days between the two dates, taking into account the start and the end days of the period selected as well as the time stamps on those days. For example, if the start time of the start date is 3 PM, the start day is not counted as a full day. The expression is formatted as follows: DATEDIFF(date1, date2)

It might be helpful to share what your data is that you are using to help validate but the formula in the column should look something like this:

displayname=Week Day Difference
textmode=true
valueexpression=WEEKDAYDIFF({date1},{date2})
valueformat=HTML
CopyToggle Text Wrapping

[KatelynnKe2]

Hi Kellie, 

Thank you for your response, please see the screenshots below. For the task at the bottom that is complete, there is a calculated field on a custom form that is providing a date that the task should be completed by. I want to have a column that provides the number of days early or late the task was completed. I am new to workfront so appreciate any resources or redirection if this is not the way to accomplish this. 

Thank you!

Report:

Compliance Column:

[Sven-iX]

Hi @KatelynnKe2 this looks good... 

I assume the field ProjectGoLiveDateTaskLevel is a field on the task: Can you try adding "DE:" just after the curly bracket, in front of "ProjectGoLiveDateTaskLevel"

[KatelynnKe2]

Thank you for looking this over, I tried that as well with no results. 

[Sven-iX]

Question: what is the calculation in the field "ProjectGoLiveDateTaskLevel" ?
I'm trying to build what you have - the diff works for me. 
You could try deleting the report and starting over; I've seen some glitches where the report editor seems in a bad state and starting over with the exact same column - worked. 

the calculated column:

valueexpression=DATEDIFF({actualCompletionDate}, {DE:ProjectGoLiveDateTaskLevel})
valueformat=string
displayname=DATEDIFF({actualCompletionDate}, {DE:ProjectGoLiveDateTaskLevel})

using the calc field

[KatelynnKe2]

Success! Removing the spaces from the custom form and then using that syntax resolved the issue. Thank you!

[JessicaBCrum]

@KatelynnKe2 Just FYI, you did not need to remove the spaces in your custom calculated field name, you just needed to correctly add it to your calculated column with the spaces. For example:

If your calculated custom field name was "Project Go Live Date Task Level"

valueexpression=DATEDIFF({actualCompletionDate},{ DE:Project Go Live Date Task Level}
displayname=Add whatever you want here for your displayname on the report column
valueformat=HTML


I hope that helps!

[KatelynnKe2]

@KellieGardner 

[KellieGardner]

Sorry not sure how some of the extra text copied into the code. You don't need "copytoggle=text wrapping"

displayname=Week Day Difference
textmode=true
valueexpression=WEEKDAYDIFF({date1},{date2})
valueformat=HTML

The syntax for a custom field vs a native field is a little different. For your custom field it still requires spaces and the use of the DE: as @Sven-iX mentioned.

See example below:

I would try this:

displayname=Day Difference
textmode=true
valueexpression=DATEDIFF({actualCompetionDate},{DE:Project Go Live Date Task Level})
valueformat=HTML

One way to verify how the syntax would be is to add the field as a column to a report, switch to text mode and look at the field name structure. Example below:

Custom field

Native Field

[KatelynnKe2]

Thank you - I confirmed the syntax is correct using the method above. The report still does not provide a value in the calculated column. I am going to open a support ticket on this.