Future expiration date renewal

2phat4u

New Member
Joined
Aug 20, 2013
Messages
45
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 20, 2013
Messages
45
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
Joined
Aug 20, 2013
Messages
45
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.
1617743880645.png
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 20, 2013
Messages
45
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
Joined
Aug 20, 2013
Messages
45
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()
 

Watch MrExcel Video

Forum statistics

Threads
1,129,737
Messages
5,638,088
Members
417,004
Latest member
soso2727

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top