# 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

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### jtakw

##### Well-known Member
Hi,

What's in A1?

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

#### 2phat4u

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

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

##### New Member

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

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

##### New Member

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

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

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
ABC
14/5/2021TRUEFALSE
26/7/2020TRUEFALSE
34/7/2018TRUEFALSE
47/8/2015FALSETRUE
512/12/2017TRUEFALSE
64/4/2017FALSETRUE
74 YearsMore than
8or Less4 Years
9YellowRed
Sheet897
Cell Formulas
RangeFormula
B1:B6B1=EDATE(A1,48)>=TODAY()
C1:C6C1=EDATE(A1,48)<TODAY()

#### 2phat4u

##### New Member
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
ABC
14/5/2021TRUEFALSE
26/7/2020TRUEFALSE
34/7/2018TRUEFALSE
47/8/2015FALSETRUE
512/12/2017TRUEFALSE
64/4/2017FALSETRUE
74 YearsMore than
8or Less4 Years
9YellowRed
Sheet897
Cell Formulas
RangeFormula
B1:B6B1=EDATE(A1,48)>=TODAY()
C1:C6C1=EDATE(A1,48)<TODAY()
Question. Is there a way without adding an extra column for tru and false?

#### jtakw

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

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,415
Messages
5,837,116
Members
430,477
Latest member
roseinsydney

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