Actual % Complete vs. On Schedule % Complete

Hope everyone is doing well? I'm looking for a formula or suggestions to accomplish the following:

IF “On schedule % Complete is > Actual % Complete then the task is “Late”, “Red Ball”.

IF “On schedule % Complete is < Actual % Complete then the task is “On track”, “Green Ball”.

IF “On schedule % Complete is = 0% (Actual % Complete) then the task is “Not Started”, “Grey Ball”.


Actual % Complete is currently being given based on progress from task owners. “On Schedule % Complete” is an automated value based on the task duration. For example, if I have a task that is 5 days long, each workday would be worth 20% complete, 100% at the end. However, if at end of 5 days the task owner reports 25%, then the task would be late. But we can also predict that the new end date will be 3 weeks/ 15 days from now based on the current reported progress.

Any information would be greatly appreciated,

Thank you.



  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Steve Akana

    Looks like your question has two parts maybe?

    1. Create a Symbol column with Red, Yellow, Green, Gray balls
    2. Use a nested if statement like this: =IF([On schedule % Complete]@row > [Actual % Complete]@row, "Red", IF(“[On schedule % Complete]@row < [Actual % Complete]@row, "Green", IF([On schedule % Complete]@row = [Actual % Complete]@row, "Gray", "")))

    If your second question is "Can the duration be automatically adjusted, the answer is currently no. The duration cannot be a formula.

    I hope this was helpful.


    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!