Conditional Formatting with an if satement

[mhassan]

Good afternoon. I need help with a formula. I need to show a red font if the date has gone past 10 days and yellow font if the date is past 7 days

 

[etaf]

use conditional format rules with formulas
2 rules needed

=AND(A2<>"",A2<TODAY()-10)
and
=AND(A2<>"",A2<TODAY()-7)

Order the RED formula first in the list and STOP if TRUE

i have added column C and D so you can see the formula working, not required for conditional formatting , just FYI

you can choose just to format the font colour, i also formatted the fill as well for display here purposes

Book1
	A	B	C	D
1			10 days	7day
2	2/27/21		TRUE	TRUE
3	2/28/21		TRUE	TRUE
4	3/1/21		TRUE	TRUE
5	3/2/21		TRUE	TRUE
6	3/3/21		TRUE	TRUE
7	3/4/21		TRUE	TRUE
8	3/5/21		TRUE	TRUE
9	3/6/21		TRUE	TRUE
10	3/7/21		TRUE	TRUE
11	3/8/21		TRUE	TRUE
12	3/9/21		TRUE	TRUE
13	3/10/21		TRUE	TRUE
14	3/11/21		TRUE	TRUE
15	3/12/21		TRUE	TRUE
16	3/13/21		TRUE	TRUE
17	3/14/21		TRUE	TRUE
18	3/15/21		FALSE	TRUE
19	3/16/21		FALSE	TRUE
20	3/17/21		FALSE	TRUE
21	3/18/21		FALSE	FALSE
22	3/19/21		FALSE	FALSE
23	3/20/21		FALSE	FALSE
24	3/21/21		FALSE	FALSE
25	3/22/21		FALSE	FALSE
26	3/23/21		FALSE	FALSE
27	3/24/21		FALSE	FALSE
28	3/25/21		FALSE	FALSE
29	3/26/21
Sheet1

Cell Formulas
Range	Formula
C2:C28	C2	=A2<TODAY()-10
D2:D28	D2	=A2<TODAY()-7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A29	Expression	=AND(A2<>"",A2<TODAY()-10)	text	YES
A2:A29	Expression	=AND(A2<>"",A2<TODAY()-7)	text	YES

 

[mhassan]

I am having trouble with the conditional formatting. I can't get the dates to change font color to reflect 10 days red font and 7 days yellow font.

 

[etaf]

did you setup the conditional formatting
And are you sure the column with dates are real dates ?
try adding XL2BB addin - see signature / menu item and post a sample here OR post on a share like onedrive or dropbox

to add a condtional format
select the column - lets say the dates are in column A
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

A2:A1000


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=AND(A2<>"",A2<TODAY()-7)


Format [Number, Font, Border, Fill] USE Font YELLOW
STOP IF TRUE
choose the format you would like to apply when the condition is true
OK >> OK

ADD another Rule
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=AND(A2<>"",A2<TODAY()-10)


Format [Number, Font, Border, Fill] USE Font RED
STOP IF TRUE
choose the format you would like to apply when the condition is true
OK >> OK

Now make sure the RED RULE is at the top of the rules, you can change the order

 

[mhassan]

did you setup the conditional formatting
And are you sure the column with dates are real dates ?
try adding XL2BB addin - see signature / menu item and post a sample here OR post on a share like onedrive or dropbox

to add a condtional format
select the column - lets say the dates are in column A
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

A2:A1000


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=AND(A2<>"",A2<TODAY()-7)


Format [Number, Font, Border, Fill] USE Font YELLOW
STOP IF TRUE
choose the format you would like to apply when the condition is true
OK >> OK

ADD another Rule
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=AND(A2<>"",A2<TODAY()-10)


Format [Number, Font, Border, Fill] USE Font RED
STOP IF TRUE
choose the format you would like to apply when the condition is true
OK >> OK

Now make sure the RED RULE is at the top of the rules, you can change the order

Thank you so much!! It worked

 

[etaf]

you are welcome