Expand my Community achievements bar.

SOLVED

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

Avatar

Level 2

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.

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

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!

View solution in original post

8 Replies

Avatar

Correct answer by
Employee Advisor

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!

Avatar

Level 3

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?

Avatar

Employee Advisor

@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!

Avatar

Level 5

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:

0694X00000EPTcdQAH.png

Avatar

Employee Advisor

@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!

Avatar

Level 5

@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! :D

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