Rolling up Health to the Parent row
Answers
-
Hi @Irene D
This formula will state exactly what you have written above, although I adjusted the order to make sure it reads through it properly.
Try this:
=IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(5), [Start Date]@row > TODAY()), "Yellow", IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Gray", IF(OR(AND([End Date]@row < TODAY(), [% Complete]@row <> 1), AND([End Date]@row < TODAY(), Status@row = "In Progress"), AND([End Date]@row < TODAY(), Status@row = "Not Started"), AND([Start Date]@row < TODAY(), Status@row = "Not Started")), "Red", IF(OR(Status@row = "Complete", [% Complete]@row = 1, AND([Start Date]@row < TODAY(), Status@row = "In Progress")), "Green", "Gray"))))
However, this doesn't account for when certain dates equal TODAY, nor does it account for any blank cells (for example, if there is no status, etc). Try it in your sheet and let me know if there are any rows where it doesn't quite work, we may need to add some additional logic in or rearrange the order of priorities.
YELLOW:
I will also note that I wrote this so that the YELLOW logic statement is if the Start Date is between Today and 5 Days from Now. Is this what you were looking for? Or did you want the Start Date to be between Today and 5 days in the past?
Ex. If today is June 2nd, did you want the status to be yellow if the Start Date was June 1st and "not started", or if it's June 3rd and "not started".
Currently I have it to turn yellow if it's June 3rd. To swap this around, change the Yellow statement to the following:
=IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY(-5), [Start Date]@row < TODAY()), "Yellow",
Here is each statement broken down:
=IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(5), [Start Date]@row > TODAY()), "Yellow",
IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Gray",
IF(OR(AND([End Date]@row < TODAY(), [% Complete]@row <> 1),
AND([End Date]@row < TODAY(), Status@row = "In Progress"),
AND([End Date]@row < TODAY(), Status@row = "Not Started"),
AND([Start Date]@row < TODAY(), Status@row = "Not Started")), "Red",
IF(OR(Status@row = "Complete", [% Complete]@row = 1,
AND([Start Date]@row < TODAY(), Status@row = "In Progress")), "Green",
"Gray"))))
Let me know if you'd like any further clarification!
Cheers,
Genevieve
-
Hi @Genevieve P. ,
Sorry for the (very) delayed response.
How could I make all the formulas below include when the dates equal today?
1) OR if "Start Date" equals today/past up to 5 business days or equal to today AND Status is "Not Started”, then Yellow
=IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(5), [Start Date]@row > TODAY()), "Yellow",
2) OR if “End Date” is in past/equals today AND % complete is not 100%, then Red
OR if "End Date" is in past/equals today AND Status is either "in Progress" or "Not Started", then Red
IF(OR(AND([End Date]@row < TODAY(), [% Complete]@row <> 1),
AND([End Date]@row < TODAY(), Status@row = "In Progress"),
AND([End Date]@row < TODAY(), Status@row = "Not Started"),
3) OR if "Start Date" is in past/equals today and Status is "In Progress", then Green
AND([Start Date]@row < TODAY(), Status@row = "In Progress")), "Green",
Also, could you kindly explain to me if this formula will even work for parent rows in my sheet? How does it work exactly for parent rows?
Thanks!
Irene
-
Hi @Irene D
No problem at all! To note when something equals today, you just need to add in the = sign before the TODAY function.
>= TODAY()
See this Help Article that has a list of Formula Operators.
I'll show you on one of the statements, but it would be the same for all of them:
1) OR if "Start Date" equals today/past up to 5 business days or equal to today AND Status is "Not Started”, then Yellow
=IF(AND(Status@row = "Not Started", [Start Date]@row <= TODAY(5), [Start Date]@row >= TODAY()), "Yellow",
Full Formula with = sign:
=IF(AND(Status@row = "Not Started", [Start Date]@row <= TODAY(5), [Start Date]@row >= TODAY()), "Yellow", IF(AND(Status@row = "Not Started", [Start Date]@row >= TODAY()), "Gray", IF(OR(AND([End Date]@row <= TODAY(), [% Complete]@row <> 1), AND([End Date]@row <= TODAY(), Status@row = "In Progress"), AND([End Date]@row <= TODAY(), Status@row = "Not Started"), AND([Start Date]@row <= TODAY(), Status@row = "Not Started")), "Red", IF(OR(Status@row = "Complete", [% Complete]@row = 1, AND([Start Date]@row <= TODAY(), Status@row = "In Progress")), "Green", "Gray"))))
This formula always looks in its own row, which means that for a Parent Row it will look at the current row's Status, Start Date, and End Date columns in the Parent row. It will not read any of the Child rows, if that's what you're asking about.
Let me know if I can clarify anything further!
Cheers,
Genevieve
