Calculated Field to Convert Project Planned Hours to "Tier" (or Effort Level) | Community
Skip to main content
Angie_S
Angie_S
Level 3
December 2, 2024
Solved

Calculated Field to Convert Project Planned Hours to "Tier" (or Effort Level)

  • December 2, 2024
  • 1 reply
  • 527 views

I'm trying to write a calculated text field that will make it so that we no loner have to manually select a "Tier" for each of our projects on their custom forms.

 

Use Case: Current-state, after a request is converted to a project and the project is built out, the project manager looks at the total planned hours for the project and then selects the correct corresponding "Tier" in a custom form. We'd like to make a calculated field that automatically picks the correct tier instead of the project manager needing to do it (and possibly selecting the wrong tier, which goes into our reports and is something we have to fix fairly regularly).

 

Example: Tier 1 projects are >=100 project planned hours, Tier 2 are <= 50 project planned hours hours, Tier 3 projects are <=25 project planned hours

 

Every code I have written has failed to accurately convert (project) planned hours to a tier. The code below will assign a 45.5 planned hour project to "Tier 1" even though it should be Tier 2 ( because 45.5 is lesser than or equal to 50). I'm sorry to say I've spent hours trying to figure this out - any thoughts on what I am doing wrong?

 

IF({workRequired}<=25,"Tier 3",IF({workRequired}<=50,"Tier 2",IF({workRequired}<=100,"Tier 1",IF({workRequired}>=101,"Tier 1","Tier 1"))))
 
    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Angie_S

    Thanks @skyehansen , I think that's the nudge that did the trick! I re-wrote my code as follows:

    IF(DIV({workRequired},60)<=25,"Tier 3",IF(DIV({workRequired},60)<=50,"Tier 2",IF(DIV({workRequired},60)<=100,"Tier 1",IF(DIV({workRequired},60)>=101,"Tier 1","Tier 1"))))
     
    And now each project that the custom form is applied to accurately reports the tier. Thanks for helping this noob!

    1 reply

    skyehansen
    Community Advisor
    skyehansenCommunity Advisor
    December 2, 2024

    without actually addressing your calculation, have you considered putting a test field on your form, to just see what "workRequired" is? I put one on my form, and 3 hours is coming out as "180" so I'm guessing you're failing to account for a conversion from minutes to hours, for one thing. I mean, your calculation may be problematic in other ways as well, but I would start with planning out some good troubleshooting measures, and at the very least, multiply all the numbers in your current equation by 60.

      Angie_S
      Angie_SAuthorAccepted solution
      Level 3
      December 3, 2024

      Thanks @skyehansen , I think that's the nudge that did the trick! I re-wrote my code as follows:

      IF(DIV({workRequired},60)<=25,"Tier 3",IF(DIV({workRequired},60)<=50,"Tier 2",IF(DIV({workRequired},60)<=100,"Tier 1",IF(DIV({workRequired},60)>=101,"Tier 1","Tier 1"))))
       
      And now each project that the custom form is applied to accurately reports the tier. Thanks for helping this noob!
        Terms & ConditionsAccessibility statement

        Loading footer...