Planned % Complete

Options

Hi,

I am trying to add a formula to my project plan in order that I can determine what the planned % completion should be.

I found a thread that recommended using =NETWORKDAYS([Start Date]@row, [Column18]131) / NETWORKDAYS([Start Date]@row, [End Date]@row)


However, this returns:

">" 100% if today is ">" than the planned end date and

"<"0% of the task is not yet scheduled to start.

I have therefore tried to use IF but I keep getting #INCORRECT ARGUMENT SET

=IF(OR(NETWORKDAYS([Start Date]@row, [Column18]131) / NETWORKDAYS([Start Date]@row, [End Date]@row), >0.99, 1, IF(NETWORKDAYS([Start Date]@row, [Column18]131) / NETWORKDAYS([Start Date]@row, [End Date]@row) < 0, 0, IF(OR(NETWORKDAYS([Start Date]@row, [Column18]131) / NETWORKDAYS([Start Date]@row, [End Date]@row), >0, NETWORKDAYS([Start Date]@row, [Column18]131) / NETWORKDAYS([Start Date]@row, [End Date]@row < 1, NETWORKDAYS([Start Date]@row, [Column18]131) / NETWORKDAYS([Start Date]@row, [End Date]@row)))))))


I want the formula to return:

1 (100%) if the planned completion is ">" 1,

0 (0%) if the planned completion is "<" 0

OR if none of the above apply return the result from the original formula of NETWORKDAYS([Start Date]@row, [Column18]131) / NETWORKDAYS([Start Date]@row, [End Date]@row)


Thank you 😊

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!