# Future expiration date renewal

#### 2phat4u

##### New Member
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?

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

##### New Member
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### jtakw

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### Well-known Member

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()?
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
ABCD
14/5/2021TRUEFALSEFALSE
26/7/2020TRUEFALSEFALSE
34/7/2018TRUEFALSEFALSE
47/8/2015FALSEFALSETRUE
512/12/2017FALSETRUEFALSE
64/9/2017FALSETRUEFALSE
73 YearsMore thanMore than
8or Less3 Years4 Years
9Less thanRed
10or equal
11to 4 Years
Sheet897
Cell Formulas
RangeFormula
B1:B6B1=EDATE(A1,36)>=TODAY()
C1:C6C1=AND(EDATE(A1,36)<TODAY(),EDATE(A1,48)>=TODAY())
D1:D6D1=EDATE(A1,48)<TODAY()

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

#### jtakw

##### Well-known Member
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

##### New Member
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

##### New Member
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()

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,412
Messages
5,837,091
Members
430,476
Latest member
chmohsin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back