% Schedule Complete Calculation

I am using a formula to calculate the % schedule complete based on project start and end date.

=(NETWORKDAYS([Start Date]1, TODAY(), Holidays2:Holidays10)) / (NETWORKDAYS([Start Date]1, [End Date]1, Holidays2:Holidays10))

Is there a way to get this formula to just return 100% if the % complete is above 100%?

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Try this:

    =IF((NETWORKDAYS([Start Date]1, TODAY(), Holidays2:Holidays10)) / (NETWORKDAYS([Start Date]1, [End Date]1, Holidays2:Holidays10)) > 1, 1, (NETWORKDAYS([Start Date]1, TODAY(), Holidays2:Holidays10)) / (NETWORKDAYS([Start Date]1, [End Date]1, Holidays2:Holidays10)))

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Rachel Kelly

    I would use the IF syntax.

    "=IF( Your Formula >= 1, 1, Your Formula)"

    =IF((NETWORKDAYS([Start Date]1, TODAY(), Holidays2:Holidays10)) / (NETWORKDAYS([Start Date]1, [End Date]1, Holidays2:Holidays10))>=1,1,(NETWORKDAYS([Start Date]1, TODAY(), Holidays2:Holidays10)) / (NETWORKDAYS([Start Date]1, [End Date]1, Holidays2:Holidays10)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!