Conditional formatting and formula help!

Question

Hi, I really could do with some help. I have a huge spreadsheet of data that I need to add some rules, to provide visual info to teams. I need to be able to do several things:

(1) When a completion date is added to a cell I need the whole row that contains that cell to change colour to grey.

(2) I need to be able to identify our overdue cases 1 day past their due date but also ignore cases where they were overdue but have been completed.

(3) I need to identify cases that are coming up, meaning they are due in 7 days time, so we can get ahead of the game before they become overdue.

can anyone help me with the formulas please?

Sample data:

REQUEST	DUE	ALLOCATED	PREDICTED	COMPLETED
01/01/2024	29/01/2024	03/01/2024	03/02/2024
02/01/2024	30/01/2024	04/01/2024	04/02/2024
03/01/2024	31/01/2024	05/01/2024	05/02/2024	31/01/2024
04/01/2024	01/02/2024	06/01/2024	06/04/2024
05/01/2024	02/02/2024	07/01/2024	07/04/2024
06/01/2024	03/02/2024	08/01/2024	08/02/2024
07/01/2024	04/02/2024	09/01/2024	09/02/2024
08/01/2024	05/02/2024	10/01/2024	10/02/2024
09/01/2024	06/02/2024	11/01/2024	11/02/2024
10/01/2024	07/02/2024	12/01/2024	12/02/2024
11/01/2024	08/02/2024	13/01/2024	13/02/2024
12/01/2024	09/02/2024	14/01/2024	14/02/2024
13/01/2024	10/02/2024	15/01/2024	15/02/2024
14/01/2024	11/02/2024	16/01/2024	16/02/2024
15/01/2024	12/02/2024	12/01/2024	17/02/2024

Answer 1

Hi macasam

Please, try the following conditional formatting rules

Note: I made a few changes in the DUE column dates to match your requirements for the example.

For GRAY try the formula =COUNTA($A2:$E2)=5

For YELLOW Due in less than 7 days

=AND(DAYS($B2,TODAY())>=0,DAYS($B2,TODAY())<=7,COUNTA($A2:$E2)<5)

For RED ==>> Expired and Not completed =AND(DAYS($B2,TODAY())<0,COUNTA($A2:$E2)<5)

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Try this one.

=$E2<>""

=AND($E2="",$B2>TODAY())

=AND($E2="",$B2>TODAY()-7)