IF Formula assistance.

This discussion was created from comments split from: Task Completion Percentages.


  • TitleVI2023
    TitleVI2023 ✭✭
    edited 04/26/24

    Hi @Hollie Green, @Genevieve P. and @parulmishra, I need help with your formula. I'm using it on a project task management sheet. The issue is with tasks with start and end dates in the future. The result for these tasks is a negative percentage of completion. This affects my project's overall progress on the summary page, as it averages the percentages from each task. What is the best course of action to resolve this problem?

    =IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete"), 1, IF(Status@row <> "Complete", NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row), 1))

  • Jgorsich
    Jgorsich ✭✭✭

    You could simply wrap the whole think in another if statement that forces a 0 if the start date is greater than today(). That would likely be the simplest.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!