Help with a formula!!

Amy M Metzger

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

ro.fei

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. 😊