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

 

[jtakw]

Hi,

What's in A1?

Show a few samples if it could be different/change.

 

[2phat4u]

Hi,

What's in A1?

Show a few samples if it could be different/change.

A1 is just a date and it is executed today 4/5/2021. Thanks

 

[jtakw]

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() )

A1 is just a date and it is executed today 4/5/2021. Thanks

Doesn't look like A1 is just a date, there something else in there?
You're using the formula to Extract the date, if it's Just a date, then =A1<TODAY() would have worked.

 

[2phat4u]

Doesn't look like A1 is just a date, there something else in there?
You're using the formula to Extract the date, if it's Just a date, then =A1<TODAY() would have worked.

I have several dates in each cell. I’m just using cell A1 as an example and the date that I picked was random from one of my other cell. I need the cell to turn yellow within 4 year and turn red when it reaches 5 years. Sorry for the confusion. Thanks in advance

 

[jtakw]

If you have Several dates in Each cell, then they're Not dates, they're TEXT, not numbers, that's why I ask for you to show samples of possible data, otherwise, you're going to get Wrong answers from people trying to help you by just guessing.

Unless, you're saying there's ONLY 1 date in the subject cell, And it's a Real Date value (number)
Does =ISNUMBER(A1) return TRUE?

 

[2phat4u]

If you have Several dates in Each cell, then they're Not dates, they're TEXT, not numbers, that's why I ask for you to show samples of possible data, otherwise, you're going to get Wrong answers from people trying to help you by just guessing.

Unless, you're saying there's ONLY 1 date in the subject cell, And it's a Real Date value (number)
Does =ISNUMBER(A1) return TRUE?

I understand what you’re saying. Yes, you are correct. Only 1 date per cell and they are formatted as date. Bottom is how to what similar to the spreadsheet that I have.

A1: 4/5/2021
A2: 6/7/2020
A3: 4/7/2018
A4: 7/8/2015
A5: 12/12/2017

 

[jtakw]

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?

Ok, Assuming you meant:

Turn Yellow, when A1 date is 4 years or Less
Turn Red, when A1 date is Over 4 years

Use these formulas for CF:

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

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

 

[2phat4u]

Ok, Assuming you meant:

Turn Yellow, when A1 date is 4 years or Less
Turn Red, when A1 date is Over 4 years

Use these formulas for CF:

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

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

Question. Is there a way without adding an extra column for tru and false?

 

[jtakw]

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.