I have automatic color dots, how do I add the If status (Due Date) is complete, return gray?

Jeff Houp

=IF([Due Date]3 < TODAY(), "Red", IF([Due Date]3 = TODAY(), "Yellow", IF([Due Date]3 > TODAY(), "Green")))

currently using the above formula to automate color dots for task health, however when an item goes complete it stays as RED. I'd like it report out as a gray.

Miles Boyea

Are you using a checkbox to confirm when the task is completed or just typing "Complete" in another cell? Let me know which one you are using and the name of that column you are pulling the "Complete" status from.

Jeff Houp

Hi Miles,

in my column [Due Date] it is a drop down not a checkbox

Miles Boyea

Hi Jeff

Can you attach a screenshot so I can see specifically how you have it setup. It's just easier to see, the drop-down should be in a different column to your [Due Date].

Miles

Paul Newcome

Try this...

=IF([Due Date]3 = "Complete", "Gray", IF([Due Date]3 < TODAY(), "Red", IF([Due Date]3 = TODAY(), "Yellow", IF([Due Date]3 > TODAY(), "Green"))))

Jeff Houp

@Miles Boyea

here's a screenshot, the "Status" column is a drop down, and the due date is an autopopulated date, based on the entry date.

Jeff Houp

@Paul, I tried using your formula. But the portion =IF([Due Date]3 = "Complete", "Gray", IF([Due Date]3 < TODAY(), "Red", IF([Due Date]3 = TODAY(), "Yellow", IF([Due Date]3 > TODAY(), "Green"))))

The first italic Due Date, needs to be the *Next Steps* column, but when I change it to that. nothing happens. i don't know much about IF/AND/THEN logical statements. Basically I'm reference 2 different columns. Stating that if "complete" is listed go green, which references the "Next steps" column, but if its not "complete", reference the other portion of the formula that says (=IF([Due Date]3 < TODAY(), "Red", IF([Due Date]3 = TODAY(), "Yellow", IF([Due Date]3 > TODAY(), "Green")))) for the health of the task.

Thanks all!

Miles Boyea

Hey Jeff

Try this

=IF([Next Steps]3 = "Complete", "Gray", IF([Due Date]3 < TODAY(), "Red", IF([Due Date]3 = TODAY(), "Yellow", IF([Due Date]3 > TODAY(), "Green", "Gray"))))

Jeff Houp

That worked like a charm @Miles Boyea much appreciated1

Miles Boyea

All is well 👍🏼

Cheers

Kathy Benner

Thank you both, I was having a similar issue issue but was using a check box for completed and wanted to Yellow 7 days before due date and with some trial and error this is what I finally came up with after you posted that this morning... works like a dream!! Much appreciated to you both.

=IF(Completed@row = 1, "Gray", IF([Due Date]@row <= TODAY(+7), "Yellow", IF([Due Date]@row > TODAY(-7), "Green", IF([Due Date]@row > TODAY(), "Red", "Gray"))))

Isaac Gallardo

Quick question. I created something similar. However, in my case is much simpler. If my status column turns Green the completion date comes today's date.

The problem I am having is that the green dot in the status column is always green. The Completion date keeps updating to today's date. How do I fix that? I wanted to make it easier for users to just check the status column to GREEN and the completion date will auto-populate.

Paul Newcome

@Isaac Gallardo You would use a Record A Date automation instead of a formula.

Andrée Starå

Hi @Isaac Gallardo

I hope you're well and safe!

I'd recommend using a Workflow combined with the Record a Date Action instead.

Would that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Isaac Gallardo

Can you give me an example? I am not following

Isaac Gallardo

I got it! I got it. Thanks