Formula

Hello All,

I am trying to write a formula on a Status column. the conditions are:

Not Started	if planned start date is in the future and actual start date is blank.
In Progress	if actual start date is not blank and its not pasted planned start date
Delayed	if actual start date is blank and today's date is passed planned start date
Past Due	if you go past the planned end date and there is no actual end date populated
Completed	if actual end date is filled in

also i want to include a children function in the formula. lets say a parent task has 2 or more children tasks that have any of the above statuses than change the parent status to the 2 or more children statuses.

Example: Parent task has 5 child tasks. if two or more of those child tasks have a Delayed status based on the conditions above than change the parent status to Delayed.

Find more posts tagged with

Formulas

Child Rows

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Tony Oxa

=IF(COUNT(CHILDREN(Task@row)) > 0, IF(COUNTIF(CHILDREN(Status@row), "Delayed") >= 2, "Delayed", IF(COUNTIF(CHILDREN(Status@row), "Past Due") >= 2, "Past Due", IF(COUNTIF(CHILDREN(Status@row), "Not Started") >= 2, "Not Started", IF(COUNTIF(CHILDREN(Status@row), "In Progress") >= 2, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Completed") >= 2, "Completed"))))), IF(NOT(ISBLANK([Actual End]@row)), "Completed", IF(AND(ISBLANK([Actual End]@row), TODAY() > [Planned End]@row), "Past Due", IF(AND(ISBLANK([Actual Start]@row), TODAY() > [Planned Start]@row), "Delayed", IF(AND(NOT(ISBLANK([Actual Start]@row)), TODAY() <= [Planned End]@row), "In Progress", IF(AND(ISBLANK([Actual Start]@row), TODAY() <= [Planned Start]@row), "Not Started", "In Progress"))))))

=IF(COUNT(CHILDREN(Task@row)) > 0,
IF(COUNTIF(CHILDREN(Status@row), "Delayed") >= 2, "Delayed",
IF(COUNTIF(CHILDREN(Status@row), "Past Due") >= 2, "Past Due",
IF(COUNTIF(CHILDREN(Status@row), "Not Started") >= 2, "Not Started",
IF(COUNTIF(CHILDREN(Status@row), "In Progress") >= 2, "In Progress",
IF(COUNTIF(CHILDREN(Status@row), "Completed") >= 2, "Completed"
))))),
IF(NOT(ISBLANK([Actual End]@row)), "Completed",
IF(AND(ISBLANK([Actual End]@row), TODAY() > [Planned End]@row), "Past Due",
IF(AND(ISBLANK([Actual Start]@row), TODAY() > [Planned Start]@row), "Delayed",
IF(AND(NOT(ISBLANK([Actual Start]@row)), TODAY() <= [Planned End]@row), "In Progress",
IF(AND(ISBLANK([Actual Start]@row), TODAY() <= [Planned Start]@row), "Not Started",
"In Progress"
)))))))
)

https://app.smartsheet.com/b/publish?EQBCT=2cac58c1dc164eb1a848d92f2fc11974

Tony Oxa

Hello,

Doesnt seem to work, i tried only the first suggestion.

When i have no dates in any of the columns it should say "Not Started" but with your formula returns "Past Due". According to the status rules:

Not Started ⇒ if Planned Start is in the future and Actual Start is blank. Per your suggested formula returns "Past Due"

In Progress ⇒ if Actual Start is not blank and its not pasted Planned Start. Per your suggested formula returns "Past Due"

Delayed ⇒ if Actual Start is blank and today's date is passed Planned Start. Per your suggested formula returns "Past Due"

Past Due ⇒ if today is past the Planned Finish and Actual Finish is blank. Per your suggested formula returns "Past Due"

Completed ⇒ if Actual Finish is not blank. This one works.

The countif and the child functions seem to work but i can fully confirm when the statusing works.

Thank you for your response. Please let me know how i can fix it.

prime_nathaniel

@Tony Oxa the the first and second formulas are the same just formatted to make it easier to read. You should check each condition on its own to verify. Then recombine them all yourself. If you do that you will get:

Not Started ⇒ if Planned Start is in the future and Actual Start is blank. Per your suggested formula returns "Past Due" // this is correct

IF(AND(ISBLANK([Actual Start]@row), TODAY() <= [Planned Start]@row), "Not Started",

In Progress ⇒ if Actual Start is not blank and its not pasted Planned Start. Per your suggested formula returns "Past Due" // change this to [Planned Start]

IF(AND(NOT(ISBLANK([Actual Start]@row)), TODAY() <= [Planned End]@row), "In Progress",

Delayed ⇒ if Actual Start is blank and today's date is passed Planned Start. Per your suggested formula returns "Past Due" // this is correct

IF(AND(ISBLANK([Actual Start]@row), TODAY() > [Planned Start]@row), "Delayed",

Past Due ⇒ if today is past the Planned Finish and Actual Finish is blank. Per your suggested formula returns "Past Due" // This is correct

IF(AND(ISBLANK([Actual End]@row), TODAY() > [Planned End]@row), "Past Due",

Completed ⇒ if Actual Finish is not blank. This one works. // This is correct

IF(NOT(ISBLANK([Actual End]@row)), "Completed",

I should note that there is also a catch all condition at the end which is that anything else is "In Progress".

You should take time to document all different permutations of your use case in order for you logic to never fall into the catch all.

You have ATLEAST :
actual start x 3 states [blank, past, current]
planned start x 3 states [blank, past, current]
actual end date x 3 states [blank, past, current]
planned end date x 3 states [blank, past, current]

As you can see here the total combination of actual use cases is WAY WAY above the 5 outcomes you have logically described. Even if everything else should be "In Progress" you really do want to take time to write down every combination into a sheet and what the intended output should be.

Tony Oxa

Hello,

Actual Start has 3 conditions: Is blank, is not blank and is not passed Planned Start (in the past)

Planned Start has 1 : Is in the future

Actual Finish has 2 : is blank and is not blank

Planned Finish has 1: Is in the future.

Also i need the "Not Started" Status to be the default (if not true value) in the formula.

I feel really lost on the reason to why this is not working. I tried asking the AI help as well and it stopped generating anything after 20min.

I have written the conditions in a sheet and cant seem to understand where the conflict happens, it look like as soon as i add the Delayed and Past Due status it only returns the one that i write first.

What do you suggest?

Paul Newcome

@Tony Oxa Based on your original post, it looks like you want to work with a "majority rules" type of logic. Can you "rank" the different statuses in the event there are more than one statuses with the same count.

For example… Out of 5 child rows you have 2 Delayed, 2 In Progress, and 1 not started would end up outputting what on the parent row?

Another example would be that if you had 3 child rows, 2 are Completed and 1 is Delayed, I imagine you wouldn't want the parent row to show Completed.

In the meantime, does this work for the child rows?

=IF([End Date]@row <> "", "Completed", IF([Planned End Date]@row < TODAY(), "Past Due", IF([Actual Start Date]@row = "", IF([Planned Start Date]@row < TODAY(), "Delayed", "Not Started"), "In Progress")))

Tony Oxa

Hello Paul,

Good point even thought i would sacrifice the child parent part of the formula it still dosent solve the issue of the statuses not changing correctly. I still dont understand where the problem is. Thanks for trying

Tony Oxa

@Paul Newcome also what does the (<>"") do in a formula?

Tony Oxa

https://community.smartsheet.com/discussion/comment/479706#Comment_479706

@prime_nathaniel

Paul Newcome

@Tony Oxa <> "" means "not blank". Did my formula not work on the child rows? If not, what was it doing that it shouldn't have been doing?

Tony Oxa

Hello @Paul Newcome,

Thank for the clarification. Unfortunately did not work it gets stuck on one of the statuses its mostly when i add the Delayed and Past Due. Will get stuck on the one that i write first in the formula. Not sure what do now. Im very confused where i am making a mistake, the dont have much conditions either and i am not including the parent child. Smartsheet supposed to be smarter than me lol. Let me know if you can think of a work around i really need this.

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it along with some screenshots of rows where it is working and rows where it is not?

Tony Oxa

@Paul Newcome

formula.png

As shown in the picture the status should be in progress even thought i would like the default to be Not Started. And also in the conditions there should be some and's as well. For example: if Actual Start is blank and today's date is passed Planned Start return "Delayed". The formula that you provided doesn't have and's

Paul Newcome

The formula I provided doesn't need any AND functions because of how it is written. The logic is in there. As for the "Not Started" not being in there for those rows, the assumption was that planned dates would be entered. Once you enter planned dates, the formula will function as expected.

If you must account for planned dates being blank, what are the different possibilities and what would be the expected outputs? Will there be a planned finish but no planned start? Will there be a planned start but no planned finish? Could there be an actual start but no planned finish? Etc. Etc..

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.