Planned % Complete

Jacqué Whitlock
Jacqué Whitlock ✭✭✭
in Formulas and Functions

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 😊

· Share on FacebookShare on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!