Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Help with a formula!!

    ✭✭✭
    in Formulas and Functions

    Hello - I'm trying to set interim status due dates based on the % of time that has passed between when a task was created and when it's due. I have this formula, but it isn't returning a date for me. Can someone help? I am sure this is a classic user error, or I'm going about this wrong, but I think you will get the idea? Thank you!!

    =IF(Status@row = "25%", [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.25), IF(Status@row = "50%", [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.5), IF(Status@row = "75%", [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.75)))) + [Days extended]@row

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • ✭✭✭✭✭✭
      edited 04/03/23 Answer ✓

      Hey @Amy M Metzger ! I believe your strategy of using nested IF statements is spot on, you just have to adjust your parameters. Right now it's looking for the status to be a string & exactly match 25%/50%/75%. Assuming the Status row contains true percentages, try adjusting the parameters to ranges looking at numbers like so:

      =IF(Status@row <= 0.25, [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.25), IF(AND(Status@row > 0.25, Status@row <= 0.5), [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.5), IF(AND(Status@row > 0.5, Status@row <= 0.75), [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.75)))) + [Days extended]@row

      You may want to adjust the ranges as needed, but that's the setup you want if it's looking at percentages. Hope this helps! Let me know if it's still not working for you & I can help you out some more. 😊

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Answer ✓

      @Amy M Metzger Glad I could help! Let me know if you need any more help 😊

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭
      edited 04/03/23 Answer ✓

      Hey @Amy M Metzger ! I believe your strategy of using nested IF statements is spot on, you just have to adjust your parameters. Right now it's looking for the status to be a string & exactly match 25%/50%/75%. Assuming the Status row contains true percentages, try adjusting the parameters to ranges looking at numbers like so:

      =IF(Status@row <= 0.25, [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.25), IF(AND(Status@row > 0.25, Status@row <= 0.5), [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.5), IF(AND(Status@row > 0.5, Status@row <= 0.75), [Created Date]@row + (NETDAYS([Created Date]@row, [CAPA Due Date]@row) * 0.75)))) + [Days extended]@row

      You may want to adjust the ranges as needed, but that's the setup you want if it's looking at percentages. Hope this helps! Let me know if it's still not working for you & I can help you out some more. 😊

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Thank you so much! That helped a TON!!

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Answer ✓

      @Amy M Metzger Glad I could help! Let me know if you need any more help 😊

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions