Status If/And Formulas

Emily T.

I have a project manage sheet I'm managing that I can't seem to get the formulas right in. The first one has two parts.

1) I want the status to be green if the Planned Completion Date is equal to today or the future. The Status to be Yellow if the Planned Completion Date is 1-7 days in the past and the Status to be Red if the Planned Completion Date is 7+ days past. The Green part of the formula works but no matter what I do, everything in the past is Yellow.

=IF([Planned Completion Date]@row >= TODAY(), "Green", IF([Planned Completion Date]@row < TODAY(7), "Yellow", IF([Planned Completion Date]@row < TODAY(14), "Red")))

2) The second part, once that is figured out, is I need the colors to be adjusted where if the Status is Completed with the Planned Completion Date Target being met, I need the status to be Green.

My other question is how do I adjust the status column of a section if the line items under it has either one yellow or one red to make that section status either yellow or red? Screen shot example attached.

These would all be formulas in the same column.

Thoughts, please?

Thank you!

Screen Shot 2021-12-02 at 4.27.59 PM.png

Anupriya

The OR bracket needs to be closed.

OR(Status@row = "Completed", [Planned Completion Date]@row >= TODAY())

Krissia B.

Hello @Emily T.

Thank you for your post! Upon further review of what you are trying to accomplish, I edited your formula to make it work for the specific criteria you had placed for the statuses.

=IF([Planned Completion Date]@row >= TODAY(), "Green", IF([Planned Completion Date]@row >= TODAY(-7), "Yellow", "Red"))

For the formula to show if the children are all "Green", change the parent to "Green", if not, I used "Red". Feel free to change this too.

=IF(COUNT(CHILDREN([Task Name]@row)) = COUNTIFS(CHILDREN(Status@row), "Green"), "Green", "Red")

I found a similar post to what you are trying to do with the status changing based on the status under it.

https://community.smartsheet.com/discussion/84836/once-my-child-dots-are-all-green-is-there-a-formula-to-automatically-turn-the-parent-dot-green

Cheers~

Krissia

Emily T.

Hi @Krissia B,

Thank you so much! I've been OOO so I wasn't able to promptly respond.

That worked in changing the colors. The second part in this equation is that if the status is marked completed, I need the health to be green, also. Currently with the formula, they're showing red, as the Planned Completion Date is more than 7 days in the past. I took the formula you gave above and tried to add and an "OR" part to the formula looking at other examples online and I must have done something wrong. I get "Incorrect Argument Set" now. Would much appreciate any insight on what adjustments could be made to fix this.

=IF(OR(Status@row = "Completed", [Planned Completion Date]@row >= TODAY(), "Green", IF([Planned Completion Date]@row >= TODAY(-7), "Yellow", "Red")))

Thank you!

Emily

Anupriya

The OR bracket needs to be closed.

OR(Status@row = "Completed", [Planned Completion Date]@row >= TODAY())