Completion % based on start and finish date


Project time line is 60 days, looking for auto status update based on start and finish date when compared to Today. Trying to get -

If start date is today = 0%

If finish date is today = 100%

If start date is more than 15 days = 25%

If finish date is in less than 30 days = 50%

If finish date is in less than 15 days = 75%

So far I have

=IF(Start@row = TODAY(), "0%", IF(Finish@row <= TODAY(), "100%", IF(AND(Finish@row <= TODAY(-30), "50%", IF(Finish@row <= TODAY(-15), "75%")))))

but getting ##INCORRECT ARGUMENT. Help please.


  • akshayd
    akshayd ✭✭

    Updates formula -

    =IF(Start@row = TODAY(), "0%", IF(Finish@row <= TODAY(), "100%", IF(AND(Start@row = TODAY(+15), "25%", IF(Finish@row = TODAY(-30), "50%", IF(Finish@row = TODAY(-15), "75%"))))))

    still doesn't work

  • James Keuning
    James Keuning ✭✭✭✭✭

    I feel like things fall apart here:

    IF(AND(Start@row = TODAY(+15), "25%", 

    Because that 25% all alone there is not something that can be tested with an IF statement, which is what you are asking to do with that AND there. I recommend you test each of your conditions on its own in a cell, and once they are all working, nest them.

    When I need to nest IF statements, I create one IF per column, where I specify the TRUE condition, and I make the FALSE condition 999. Once the all work, I move from right to left, copying the right-most formula over the 999 in the second from the right, and then copying that over the 999 in the third from the right, and I nest everything until I am to the left.

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭

    Hey @akshayd!

    A couple questions, then comments / tips & then a potential solution-

    Is this for multiple projects & they're all 60 day timeframes regardless?

    The major issue you're running into is you don't the the AND statement - you don't finish it off & you're only checking 1 thing at a time- that 's the main reason for the error.

    I also Highly recommend (if your timeframes are always 60 days) - base your statements all on the same cell, rather than switching between Start & Stop column.

    That being said - if you do have varying timeframes - let me know and I'll help you work out a quick formula to get the % of time elapsed for those as well!

    Here's what I did:

    Format your Status column as a % and this will work:

    =IF(Finish@row <= TODAY(), 1, IF(Finish@row <= TODAY(15), 0.75, IF(Finish@row <= TODAY(30), 0.5, IF(Finish@row <= TODAY(45), 0.25, IF(Finish@row > TODAY(45), 0, "")))))

    As @James Keuning said - basically write your IFs in reverse.

    Let me know if that works or if you need additional help!

    -Jon Mark

  • akshayd
    akshayd ✭✭

    Hi @Jon Mark H ,

    Thanks for the reply. Yes, all the projects are with 60 days timeframe. The formula you have posted works but I guess needs refinement for 60 day calculation.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!