Please help with the following formula to return '% Completion planned' with conditions:


I have the following columns for tasks:

  • Planned start date
  • Due date
  • Planned duration; calculated as =1 + ([Due date]@row - [Planned start date]@row)
  • Days remaining or overdue; calculated as =[Due date]@row - TODAY()
  • % Completion planned; calculated as = 1 - ([Days remaining or overdue]@row / [Planned duration]@row)

I am trying to return the '% Completion planned' for tasks with the following conditions:

  1. Return 0% if tasks are planned (e.g. Today's date < planned start date)
  2. Return the % Completion planned based in the above calculation if tasks are in progress (e.g. Planned start date < Today's date < Due date)
  3. Return 100% if tasks are late (e.g. Today's >date due date)

For the above conditions I use the following IF statement:

=IF([Days remaining or overdue]@row > 0; 1 - ([Days remaining or overdue]@row / [Planned duration]@row); "100%")

The problem is this formula returns condition 2 & 3 correctly, but returns a negative % for condition 1, e.g. tasks planned (Today's date < planned start date)

Please assist with the formula so that all 3 conditions are met.



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!