Future expiration date renewal

[2phat4u]

I need help. How do I use conditional formatting for the date in cell A1 to turn yellow when it is within a 4 years of renewal preparation and turn red when it is in a 5 years for a renewal?

Below is the formula I've used but it seem not to be working. Please help....

Use a formula to determine which cells to format
Red color
=OR( DATEVALUE(MID(A1,2,SEARCH(")",A1,2)-2)) < TODAY(), DATEVALUE(MID(A1,SEARCH("(",A1,2)+1,SEARCH(")",A1,2)-2))< TODAY() )

Use a formula to determine which cells to format
White color
put a check mark if Stop If True
=ISBLANK(A1)=TRUE

Format only cells that contain
red color
Cell value >>> greater than >>> =NOW()+1825

Format only cells that contain
Yellow color
Cell value >>> greater than >>> =NOW()+1460

 

[2phat4u]

You Don't need to Add Anything.

I'm showing the Columns to demonstrate TRUE/FALSE results Only. You don't need them at all.

Just use the formulas for CF.

Thanks

 

[jtakw]

You're welcome.

Post back if it's not exactly what you need, I'll be more than happy to make modifications if needed.

 

[2phat4u]

Questions. On the formula #2 below, is the equal = sign supposed to be in the formula after < sign?

1) =EDATE(A1,48)>=TODAY()
2) =EDATE(A1,48)<TODAY()


My other question is, if the date are within 3 years, how do I make it turn white? Would the formula be like this =EDATE(A1, 36)<=TODAY()?

 

[2phat4u]

You're welcome.

Post back if it's not exactly what you need, I'll be more than happy to make modifications if needed.

I'm having trouble doing one column. One column has about 3605 row. When I applied the formula you gave me, I added in for column B and some of the blank cells turn red and the date that has been over 5 years did not turn Red and did not turn yellow with 4 years less.
I'm trying to manipulate the date for column D, F, H and it would not work. Please help.

 

[jtakw]

Questions. On the formula #2 below, is the equal = sign supposed to be in the formula after < sign?

1) =EDATE(A1,48)>=TODAY()
2) =EDATE(A1,48)<TODAY()

My other question is, if the date are within 3 years, how do I make it turn white? Would the formula be like this =EDATE(A1, 36)<=TODAY()?

Answer to your 1st question regarding = sign.
No, do not add the equal sign, otherwise, On the 4 year anniversary Date, Both conditions will be TRUE.
Conditions are as I described in Post #8

For your second question, see below.

B1 CF formula for 3 Years and Less
C1 CF formula for More than 3 Years, up to and Including 4 Years
D1 CF formula for More than 4 Years and on

Book3.xlsx
	A	B	C	D
1	4/5/2021	TRUE	FALSE	FALSE
2	6/7/2020	TRUE	FALSE	FALSE
3	4/7/2018	TRUE	FALSE	FALSE
4	7/8/2015	FALSE	FALSE	TRUE
5	12/12/2017	FALSE	TRUE	FALSE
6	4/9/2017	FALSE	TRUE	FALSE
7		3 Years	More than	More than
8		or Less	3 Years	4 Years
9			Less than	Red
10			or equal
11			to 4 Years
Sheet897

Cell Formulas
Range	Formula
B1:B6	B1	=EDATE(A1,36)>=TODAY()
C1:C6	C1	=AND(EDATE(A1,36)<TODAY(),EDATE(A1,48)>=TODAY())
D1:D6	D1	=EDATE(A1,48)<TODAY()

You Don't need to add anything, just use the CF formulas.

 

[jtakw]

I'm having trouble doing one column. One column has about 3605 row. When I applied the formula you gave me, I added in for column B and some of the blank cells turn red and the date that has been over 5 years did not turn Red and did not turn yellow with 4 years less.
I'm trying to manipulate the date for column D, F, H and it would not work. Please help.

When you select your Cell/Range for CF, Make Certain that the 1st Cell you select Matches the Cell Reference of the CF formula.
Otherwise, your CF will be Offset.

For instance, if you are wanting B10:B4000 for CF, you need to change the formula cell reference to Match B10, like

=EDATE(B10,36)>=TODAY()

 

[2phat4u]

You're welcome.

Post back if it's not exactly what you need, I'll be more than happy to make modifications if needed.

Hey man,
Just wanted to provide you an update. I made a mistake and found it and fix it. It is working great. Thanks for you help. Much appreciated.
Also, I was able to modified the formula you gave me and make it to where if it is within 3 years, it will turn White.

 

[2phat4u]

When you select your Cell/Range for CF, Make Certain that the 1st Cell you select Matches the Cell Reference of the CF formula.
Otherwise, your CF will be Offset.

For instance, if you are wanting B10:B4000 for CF, you need to change the formula cell reference to Match B10, like

=EDATE(B10,36)>=TODAY()

Here's how I set it up and has to be in that order for it to work. Please let me know if you see something it is right.

**Use a formula to determine which cells to format
White color
put a check mark if Stop If True
=ISBLANK(A1)=TRUE

**Use a formula to determine which cells to format
White color
put a check mark if Stop If True
=EDATE(A1,36)>=TODAY()

**Use a formula to determine which cells to format
Yellow color
=EDATE(A1,48)>=TODAY()

**Use a formula to determine which cells to format
Red color
=EDATE(A1,48)<TODAY()