How do I use the Planned Hours field in a report calculation?

[secinarot]

I want to add a calculated column to my report showing the average number of hours per week of that task. I tried doing a formula - planned hours / duration - but it shows as blank. How do I use the Planned Hours field in a calculation?

[cabutter]

Hmmmm, a few things to be aware of.

Formulas need to be wrapped with expressions (sort of like Excel if you are familiar)

There is a list of them here. You should bookmark

You also need to use the proper names. Workfront has a few instances of confusing system names. For instance, planned hours is actually "workRequired".

You should get comfortable reading the API Explorer to find system names. Or you can select them from the UI while building reports and switch to text mode.

Eventually, you end up with something like this.

valueexpression=PROD(DIV(DIV({workRequired},60), DIV({duration}, 60, 8)), 5)

1. Our goal is time divided by duration (but we want to return hrs/week and none of our data fields return the right unit for that).
2. workRequired returns minutes so we do DIV({workRequired},60) to divide the minutes by 60
3. duration is also returned in minutes and it accounts for your work week (I am assuming 8 hour work days). So we do DIV({duration}, 60, 8) divide the minutes by 60 and then by 8
4. Finally we multiply that by a week (also assuming a work week and not a calendar week) the 5 days. PROD({....}), 5)

You will also note, when using the value of a data field in Workfront it needs to be wrapped in curly brackets {likeThis}. You can adjust numbers accordingly

[secinarot]

Thank you for the detailed explanation! I think this will work - going to try it out in the AM

[CharlieSa1]

Hey Tony, did you manage to make it work?

I am under a similar situation here - I am adding a calcualted field that would use the Fibonnaci scale to return an X amount of hours based on the input from Planned Hours, however, I can't manage to make it work, no matter what I try, the formula (I am using IF) always returns the false expression. I am guessing is because of the word "Hours" that gets added to the field once in view mode?

@Jose Mora‚

[secinarot]

No, I ended up abandoning the idea and found another way to do what I wanted to do. It was a simpler solution that didn't require a calculated field.

[CharlieSa1]

Hi Tony, I found a solution to my "problem". The Planned Hours is somehow reporting in the backend, other data different than the amount of hours, it is actually reporting minutes if I am not mistaken. So to make it work, I did enter the formula as follows, it was the only way I could get Planned Hours to show accurate data.

IF(Planned Hours<1*60,2,IF(Planned Hours>=1*60&&Planned Hours<2*60,4,... so on...

Hope it works!