symbols formula

Simon Martinez

I've created a formula based on some suggestions here where I want to track the status of tasks

There are 3 columns, CREATED, DUE DATE and COMPLETED (this is a check box)

I would like to have the symbols change

if a date is between today and the due date = green

if the date is 2 days before the due date from today, then turn yellow

if the date is on or past due date from today = red

if completed column is checked, then turn gray

IF([DUE DATE]@row <= TODAY(), "Red", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row = TODAY(), "Yellow")))

any help would be greatly appreciated!

Jeff M.

Hi Simon,

This formula should work for you.

=IF(AND([DUE DATE]@row < TODAY(), [DUE DATE]@row >= TODAY(-2)), "Yellow", IF([DUE DATE]@row < TODAY(), "Green", IF([DUE DATE]@row >= TODAY(), "Red", "")))

Also, you can change the cell to gray using the conditional formatting option at the top of the sheet next to the highlighter tool. All you need to do is select complete, is checked, and choose the cell color, then apply to the complete column only.

Simon Martinez

I tried the below formula following the suggestion -

=IF(ISBLANK(CREATED@row), "", IF(AND([DUE DATE]@row > TODAY(), [DUE DATE]@row <= TODAY(2)), "Yellow", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row <= TODAY(), "Red", ""))))

What I've noticed is that once a completed date is added, even though it might fall on green, as the days go by, it turns red?

Is there any way to freeze once a completed date has been added??