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

JeanMarcKruger

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.

Thanks!

Paul Newcome

Try this:

=MAX(MIN(1 - ([Days remaining or overdue]@row / [Planned duration]@row), 1), 0)