conditional formatting or formula to change cell color

jonylost

New Member
Joined
Nov 5, 2019
Messages
2
I am looking for help to change a row of cells color based on a date.

Here is the problem:

If a person is to leave a job on a future date (X) and I want the row of cells containing that persons data to change colors based on how close today's date is to that future date.

If the future date (X) is over 90 days away, the row is green, if X is less than 90 but greater than or equal to 30 days, the row is yellow, if X is less than 30 days away the row is red.


thank you all.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,666
Office Version
2007
Platform
Windows
Hi @jonylost, welcome to the forum!

Select cells A2:B4
Add these formulas in the following order:

First rule to green

=TODAY()+90<$B2

Second rule to yellow

=TODAY()+30<=$B2

Last to red

=TODAY()+30>$B2


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >NAME</td><td >DATE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; ">Dam</td><td style="background-color:#92d050; text-align:right; ">04/02/2020</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; ">Smi</td><td style="background-color:#ffff00; text-align:right; ">06/12/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ff0000; ">Jon</td><td style="background-color:#ff0000; text-align:right; ">20/11/2019</td></tr></table>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,666
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top