Date and checkbox formula question with multiple criteria

Shannon Scarbrough

Hello!

I would like to create a formula which populates a symbol column with the status showing these colors: red, yellow, green, and gray based on the following rules:

If the due date is in the past, and the checkbox column is unchecked, show red in the status column cell

If the due date is within the next 5 days, and the checkbox column is unchecked, show yellow in the status column cell

If the due date is more than 5 days away, and the checkbox column is unchecked, show green in the status column cell

If the checkbox column is checked, regardless of due date, show gray.

Thank you!

Heather Duff

Hi @Shannon Scarbrough,

Try this:

=if(AND([due date]@row < TODAY(),[checkbox]@row=0),"Red",if(AND([due date]@row<=TODAY(5),[checkbox]@row=0),"Yellow",if(AND([due date]@row>TODAY(5),[checkbox]@row=0),"Green",if([checkbox]@row=1,"Gray",""))))

Let me know if it works for you! Be sure your due date column is set as a date column.

Best,

Heather

Shannon Scarbrough

Hi @Heather D ,

Thank you! This is the formula I typed in exactly:

=IF(AND([Due Date]@row < TODAY(), [Check if Task has been Completed]@row = 0, "Red", IF(AND([Due Date]@row <= TODAY(5), [Check if Task has been Completed]@row = 0), "Yellow", IF(AND([Due Date]@row > TODAY(5), [Check if Task has been Completed]@row = 0), "Green", IF([Check if Task has been Completed]@row = 1, "Gray", "")))))

I've confirmed is the date column is a date column. I'm getting the incorrect augment set.

Shannon Scarbrough

Hi @Heather D

I just got it to work actually - with this formula:

=IF([Check if Task has been Completed]@row = "true", "Gray", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(5), "Yellow", "Green")))

I really appreciate your help!

Heather Duff

@Shannon Scarbrough that's a much more concise formula! Nice work!

Amelia Lutz

@Heather D I'm using Shannon's formula but I keep hitting a road block. I have been big-braining this for awhile but can't figure it out.

I have a column that I want to show if a task is past due or not based on the due date, however, I also want it to take into consideration the status and leave those prior due dates alone that have a complete status..

I am using the above formula and it will use one status type, but I cannot figure out how to get it to do multiple....

Statuses are: Not Started, Complete, and In Progress.

1. For those whose status is "Complete" with a due date that is < TODAY, I want the flag to be blank. (This below formula does that)
2. For those whose status is "Not Started" or "In Progress" with a due date that is <TODAY, I want the flag to be "Past Due." (The below formula does that)
3. For those whose status is "Not Started," "In Progress", and "Complete" with a due date that is in the future (> today), I want the flag to be blank.

Number 3 is where I am getting hung up. Here's my formula I am toying with

=IF(Status@row = "Complete", "", IF(Status@row = "Not Started", "Past Due", (IF(Finish@row < TODAY(), "Past Due", IF(Finish@row <= TODAY(5), "Past Due", "")))))

I am pretty sure I need to create another IF(Finish>Today) type but I can't figure it out.

Thank you!

Amelia Lutz

@Heather D I think I figured some of it with;

=IF(Status@row = "Complete", "", IF(Status@row = "", "", IF(Status@row = "In Progress", IF(Finish@row = "", "", IF(Finish@row < TODAY(), "Past Due", IF(Finish@row <= TODAY(1), "Past Due"))))))

Although I might be off. See what you think!

Heather Duff

Hi @Amelia Lutz !

I think I have a way to simplify it. Here's what I've got:

=if(OR(Status@row = "Complete",Status@row = "",finish@row="", finish@row >TODAY()),"","Past Due")

This translates to:

If EITHER (status is complete, status is blank, finish date is blank, or finish date is in the future), then show blank.

Otherwise, show past due.

This should cover everything. Since the formula stops once one condition is met, it'll catch anything that is complete, has no status, has no finish date, or the finish date is in the future, before it moves on to mark it past due.

Test it out and let me know if it works!

Best,

Heather

Amelia Lutz

It did, thank you!