Is there a way to calculate the actual duration for an issue?

[alferaj16]

I want to see how long it takes each issue to complete, but using actual entry dates vs actual completion dates. I do not want to use the planned dates.

[NicholeVargas · Accepted solution]

Jocelyn,

There are a few different ways to calculate this value using text mode. If you wanted to take into consideration all (calendar) days, rounded to 2 decimal places, you would use the following text mode code:

displayname=Issue Duration

valueformat=HTML

valueexpression=ROUND(DATEDIFF({actualCompletionDate},{entryDate}),2)

If you wanted to only take into considering weekdays, you would use the following text mode code:

displayname=Issue Duration

valueexpression=ROUND(WEEKDAYDIFF({entryDate},{actualCompletionDate}),2)

valueformat=HTML

Additional information regarding calculated data expressions can be found here: https://one.workfront.com/s/article/Calculated-data-expressions-679086747

Another option would be to create a calculated custom field on an Issue Custom Form so that you can group or chart by the Issue Duration value.

If you have any questions, let me know!

[alferaj16]

thank you!

[SuryaLa1]

Hi Nichole,

Is there a way to extend this to only consider working days based on a schedule in the system? Just counting the working days rather than all weekdays?

[NicholeVargas]

@Surya Lakhani‚

The calculated data expressionm, WORKMINUTESDIFF, is M-F minus any schedule exceptions found in the system default schedule. It just gives you a value measured in minutes. So you’ll usually have to divide the result by 480, which is the number of minutes in a standard 8-hour workday.

The calculation would look like the following:

displayname=Actual Duration

valueformat=HTML

valueexpression=ROUND(DIV(WORKMINUTESDIFF({Actual Completion Date},{entryDate}),480),2)

If you have any questions, let me know!

[SuryaLa1]

Thanks for sharing!

[RyanKirk]

Hi @Nichole Vargas‚ ,

Is there a good way to edit this so that when a day has passed the planned completion date it shows up as a negative?

If I just want to show weekdays it seems to work right

valueexpression=CONCAT(ROUND(WEEKDAYDIFF($$TODAYb,{plannedCompletionDate}),0), " Days")

valuefield=Weekdays to Planned Completion

description=Today to Planned Completion

textmode=true

valueformat=HTML

However when I change to show just the available workdays, the negative goes away.

valueexpression=CONCAT(ROUND(DIV(WORKMINUTESDIFF($$TODAYb,{plannedCompletionDate}),480),0), " Days")

valuefield=Weekdays to Planned Completion

description=Today to Planned Completion

textmode=true

valueformat=HTML

Sample Results:

[NicholeVargas]

@Ryan Kirk‚

This should work! I added an IF statement so that it says if the planned completion date is less than today, show as a negative, otherwise, show as a positive:

displayname=Weekdays to Planned Completion

textmode=true

valueexpression=IF($$TODAY>{plannedCompletionDate},CONCAT("-",ROUND(DIV(WORKMINUTESDIFF($$TODAYb,{plannedCompletionDate}),480),0), " Days"),CONCAT(ROUND(DIV(WORKMINUTESDIFF($$TODAYb,{plannedCompletionDate}),480),0), " Days"))

valueformat=HTML

If you have any questions, let me know!

[RyanKirk]

@Nichole Vargas‚ That does seem to work for me! I've got one project that seems to be misbehaving with it, but i probably just need to recalculate it (or test in sandbox next week)

Thank you for your help with this! 😄

EDIT: figured out the issue - items end on the same date but at different times.