Need help returning status dot based on date and checkpoint status

Alex Hackford

Hi, I have a formula to return a status symbol based on a date and checkpoint status associated w/ that date.

Here's my current formula, which seems to be working EXCEPT that I need it to return a Gray dot if the CP1 Date has not passed or is blank. Screenshot included.

=IF(OR(Status@row = "Backlog", Status@row = "Event In Progress", Status@row = "Developing Charter", Status@row = "Pre-work Underway", ISBLANK(Status@row)), " ", IF(OR(Status@row = "In Sustainment", (Status@row = "Complete")), IF(AND([CP1 Date]@row <= TODAY()), IF([CP1 Goal Met?]@row = "Yes", "Green", "Red"), IF(OR(Status@row = "In Sustainment", (Status@row = "Complete")), IF(AND([CP1 Date]@row <= TODAY()), IF([CP1 Goal Met?]@row = "No", "Red", "Gray"))))))

Any suggestions are greatly appreciated.

Alex Hackford

Thank you that worked perfectly!

Alex Hackford

Here's what's currently being returned. You'll see that on the last row in the screenshot above there is no CP1 Goal Mat? but CP1 sustainment status is still returning red instead of Gray.

Genevieve P.

Hi @Alex Hackford

It looks like you've added some closing parentheses in the wrong place. When you're using AND or OR statements, you'll want to list each instruction with commas between them versus parentheses.

For example, your formula of:

IF(OR(Status@row = "In Sustainment", (Status@row = "Complete")),

Could be:

IF(OR(Status@row = "In Sustainment", Status@row = "Complete"),

Although it should work if the entire instruction is enclosed, I think this may be where the errors in your other statements are coming from. See your structure:

 IF(AND([CP1 Date]@row <= TODAY()), IF([CP1 Goal Met?]@row = "Yes", "Green", "Red"),

If we break this down, you're closing the AND statement before the next part of your formula:

AND([CP1 Date]@row <= TODAY()) < this closes the AND, but you haven't added any instructions.

If you meant to say IF the Date is less than today AND if the Goal is Yes, then this is the structure you'd want:

 IF(AND([CP1 Date]@row <= TODAY(), [CP1 Goal Met?]@row = "Yes"), "Green", "Red"),

Then for your statement about when the Status is either "in Sustainment" or "Complete", you have the same criteria listed twice. You only need to list this once, then say what happens if it's True, or what happens if it's False.

IF(OR(Status@row = "In Sustainment", Status@row = "Complete"),

True? ----- IF(AND([CP1 Date]@row <= TODAY(), [CP1 Goal Met?]@row = "Yes"), "Green",

False? -----IF(AND([CP1 Date]@row <= TODAY(), [CP1 Goal Met?]@row = "No"), "Red",

Try this adjusted formula:

=IF(OR(Status@row = "Backlog", Status@row = "Event In Progress", Status@row = "Developing Charter", Status@row = "Pre-work Underway", ISBLANK(Status@row)), " ", IF(OR(Status@row = "In Sustainment", Status@row = "Complete"), IF(AND([CP1 Date]@row <= TODAY(), [CP1 Goal Met?]@row = "Yes"), "Green", IF(AND([CP1 Date]@row <= TODAY(), [CP1 Goal Met?]@row = "No"), "Red", "Gray")))

Keep in mind that a blank date cell is seen as "in the past", so if you have a blank date cell but the CP1 Goal Met? says "No", you'll receive a Red status ball.

Cheers!

Genevieve

Alex Hackford

Thank you that worked perfectly!