Status Formula...

I'm really struggling to get my status formula to update appropriately in my project schedule. I've tried so many different formulas and hidden columns I'm starting to lose my mind!

What I want:

If Planned start date is <today and % cmpl is 0, then 'not started'

If planned start date is >today and % complete is < the difference between time elapsed and time remaining, then 'behind schedule'

If Planned start date is >today and % complete is greater than the difference between time elapsed and time remaining then 'ahead of schedule'

If planned finish date is past and % complete is less than 1 I want overdue

If % complete is = 1 then complete (this one was easy)

Any ideas how to put this into a formula?

This was my original formula, but removing cancelled column broke it and I can't decompose it now: =IF(#REF = 1, "Cancelled", IF(AND(NOT(ISDATE([Planned Start Date]@row)), NOT(ISDATE([Planned Finish Date]@row)), [% Cmpl]@row = 0), "Not Started", IF(AND(ISDATE([Planned Start Date]@row), ISDATE([Planned Finish Date]@row)), IF([% Cmpl]@row = 1, "Complete", IF(AND([Planned Finish Date]@row <> "", [Planned Finish Date]@row < TODAY(), [% Cmpl]@row < 1), "Overdue", IF(AND([Planned Start Date]@row > TODAY(), [% Cmpl]@row = 0), "Not Started", IF([Time Elapsed]@row - [% Cmpl]@row > 0.1, "Behind Schedule", IF([% Cmpl]@row - [Time Elapsed]@row > 0.1, "Ahead of Schedule", "On Schedule"))))))))

Find more posts tagged with

Accepted answers

All comments

James Keuning

I am looking at this and have questions:

If Planned start date is <today and % cmpl is 0, then 'not started'

Can we just test for % cmpl is 0? What if the planned start date is in the future and % cmpl is 0, isn't that also 'not started'? Or are you handling that case in some other way?

If planned start date is >today and % complete is < the difference between time elapsed and time remaining, then 'behind schedule'

If planned start date is greater than today, then how would any time have elapsed? I am assuming here that if a project is planned to take 100 days, and 30 days have passed since the planned start, you want to store that 30%. And then you want to compare that to the manually updated % Cmpl field. And if the planned complete is less than the actual complete, return 'behind schedule'.

If % complete is = 1 then complete (this one was easy)

It seems like you should test for this first

If planned finish date is past and % complete is less than 1 I want overdue

and then test for this second

I recommend you put each of these formulas in a column, so that you can confirm that it gives you the proper Y/N response for a range of dates, and THEN build your IF statements one at a time, testing each before you add the next one.

Jennifer Berg

I think I may be using <> different than I intended. I have tried breaking it into multiple cells and individual formulas which I could then merge, but the IF(AND()TODAY() formula keeps giving me trouble. Let me trying stating the scenarios differently:

What I want:

If Planned start date after today (meaning in the future) and % cmpl is 0, then 'not started'.
If Planned start date is before today (meaning planned start date has passed) and % complete is less than the difference between time elapsed and time remaining, then 'behind schedule'. Said differently if today is 4/7 and the task was supposed to start on 4/5 and it's a 5 day task, then today, 4/7 the task should be 60% or greater complete because we are 3 days into a 5 day task, if less than that, then 'behind schedule"
If Planned start date is before today (meaning the planned start date has passed) and % complete is greater than the difference between time elapsed and time remaining then 'ahead of schedule' Said differently, if today is 4/7 and the task was supposed to start on 4/5 and it's a 5 day task, then today 4/7 the task should be 60% or greater complete. If it's more than 60% then 'ahead of schedule.
If planned finish date is past (meaning it's in the past) and % complete is less than 1 I want overdue
If % complete is = 1 then complete (this one was easy)

Any ideas how to put this into a formula?

Kelly Moore

Hey @Jennifer Berg

Try this.

=IF([% Complete]@row = 1, "Complete", IF(AND([Planned End Date]@row < TODAY(), [% Complete]@row < 1), "Overdue", IF(AND([Planned Start Date]@row > TODAY(), [% Complete]@row = 0), "Not Started", IF(AND([Planned Start Date]@row < TODAY(), (NETDAYS([Planned Start Date]@row, TODAY()) / Duration@row) < [% Complete]@row), "Behind Schedule", IF(AND([Planned Start Date]@row < TODAY(), (NETDAYS([Planned Start Date]@row, TODAY()) / Duration@row) > [% Complete]@row), "Ahead Schedule", IF(AND([Planned Start Date]@row > TODAY(), [% Complete]@row > 0), "Started"))))))

Note I used NETDAYS vs NETWORKDAYS to find the difference between TODAY() and Start Day. If you want only weekdays counted then the formula needs to be changed to NETWORKDAYS.

I gave you one extra category - you can delete the last IF should you desire. If the project was underway prior to the start date and %Comp, it shows "Started". Without that your cell would be blank.

cheers,

Kelly

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.