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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jtakw

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

What's in A1?

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

jtakw

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

ADVERTISEMENT

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

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

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