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
-
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. 😊
-
@Amy M Metzger Glad I could help! Let me know if you need any more help 😊
Answers
-
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!!
-
@Amy M Metzger Glad I could help! Let me know if you need any more help 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!