Help with a formula!!

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

Best Answers

  • ro.fei
    ro.fei ✭✭✭✭✭✭
    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. 😊

  • ro.fei
    ro.fei ✭✭✭✭✭✭
    Answer ✓

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

Answers

  • ro.fei
    ro.fei ✭✭✭✭✭✭
    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. 😊

  • Thank you so much! That helped a TON!!

  • ro.fei
    ro.fei ✭✭✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!