Conditional Formatting based on Status and End Date

RolandN

Hello,

I know this may be have been answered before but I would like some help with the following.

I have a sheet with

• % complete
• Status (Not Started, In Progress, Blocked, and Complete)
• Start Date and End Date

I would like to create some sort of FLAG or conditional formatting with the following logic.

1. If due date is in 3 days & status not in "Complete" —> yellow flag or yellow formatting
2. if due date is today or in the past & status not in "Complete" —> Red flag or red formatting

There may be other scenarios, but I think I might be able to add those in.

Thanks for all the help.

RN

Protonsponge

Hello @RolandN,

Would something like below get you started using a symbols column? If you need the formula tailoring to your needs, I or someone from here will happily help you.

Formula below will….

1. If [Due Date] is less than today and [Status] is anything other than complete - Red,
2. If [Due Date] is within the next 3 days and [Status] is anything other than complete - Yellow
3. If anything else - Green

=IF(AND([Due Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Due Date]@row <= TODAY() + 3, [Due Date]@row > TODAY(), Status@row <> "Complete"), "Yellow", "Green"))

I hope that is helpful to you in some way,

Protonsponge

Paul Newcome

Give this a try:

=IF(Status@row <> "Complete", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row <= TODAY(3), "Yellow")))

The above will do exactly what you have outlined in your post.

1. If due date is in 3 days & status not in "Complete" —> yellow flag or yellow formatting
2. if due date is today or in the past & status not in "Complete" —> Red flag or red formatting