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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.