Completion % based on start and finish date

akshayd

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

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

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

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

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.