How do I use the Planned Hours field in a report calculation? | Community
Skip to main content
S
secinarot
Level 2
February 10, 2022
Question

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

  • February 10, 2022
  • 1 reply
  • 1205 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

C
cabutter
Level 2
February 10, 2022

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

    S
    secinarotAuthor
    Level 2
    February 10, 2022

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

    C
    charlie89
    May 12, 2022

    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‚

    Terms & ConditionsAccessibility statement

    Loading footer...