Conditional Format to turn Red if 5 yrs out, Yellow if 4 yrs out and green otherwise

Nlhicks

Active Member
Joined
Jan 8, 2021
Messages
264
Office Version
  1. 365
Platform
  1. Windows
With this formatting, I get the Green to show up but when I change the date in column B to Aug-2011, the red does not work and when I change it to the formula for yellow, all the cells change regardless of what one I am working on.



Arc Flash Tracking 2.xlsm
ABCDEFGHIJKLMN
1SubstationDate StickeredStickered ByDue DateField Comments
2BelfieldDec-04Could not label due to Construction project blocking access, still needs to be done
3BisbeeOct-23T. McCreadySep-28
4BismarckOct-22T. McCreadySep-27Model is 99% done, waiting on Fuse verification from NDMO, have email string in folder, needs labeled still
5Cambell CountyDec-04
6CarringtonDec-04
7Custer TrailDec-04
8DenbeighDec-04
9DevaulOct-22T. McCreadySep-27New Station service transformer to be install poss. 2024/25, new xfmr info is in folder in email string. No cell service at sub, very remote, verify location prior to driving out
10Devils LakeAug-22T. McCreadyJul-27
11EdgeleyMay-23T. McCreadyApr-28
12ElliotDec-04
13FargoMay-23T. McCreadyApr-28
NDMO
Cell Formulas
RangeFormula
D2:D13D2=DATE(YEAR(B2)+$P$2,MONTH(B2),DAY(Q2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D33Expression=DATE(YEAR(B2),MONTH(B2),DayQ2)<YEAR(TODAY())-5textNO
D2:D33Expression=DATE(YEAR(B2),MONTH(B2),DAY(Q2)<=YEAR(TODAY())-5)textNO
Cells with Data Validation
CellAllowCriteria
A2:A13List=MyList2
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Ohhh, okay column P is 5
Here is what I need: If the due date is past the 5 year mark then obviously it is red.
If the due date is within the year then it is yellow
otherwise it is green.
 
Upvote 0
ok,
so no colour between > 1 year and less than 5
 
Upvote 0
colour all the cells GREEN - so that will cover every thing else

then RED use
=AND(D3<>"",D3<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())))
then yellow use
=AND(D3<>"",D3>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),D2<TODAY())

Book4
ABCDEFGHIJKLMNOPQ
1SubstationDate StickeredStickered ByDue DateField Commentsover 5less than 1
2BelfieldCould not label due to Construction project blocking access, still needs to be doneTRUEFALSE
3Bisbee45200T. McCready1/1/04TRUEFALSE
4Bismarck44835T. McCready5/30/06Model is 99% done, waiting on Fuse verification from NDMO, have email string in folder, needs labeled stillTRUEFALSE
5Cambell County10/26/08TRUEFALSE
6Carrington3/25/11TRUEFALSE
7Custer Trail8/21/13TRUEFALSE
8Denbeigh1/18/16TRUEFALSE
9Devaul44835T. McCready6/16/18New Station service transformer to be install poss. 2024/25, new xfmr info is in folder in email string. No cell service at sub, very remote, verify location prior to driving outFALSEFALSE
10Devils Lake44774T. McCready11/12/20FALSEFALSE
11Edgeley45047T. McCready4/11/23FALSETRUE
12Elliot9/7/25FALSEFALSE
13Fargo45047T. McCready2/4/28FALSEFALSE
147/3/30FALSEFALSE
1511/29/32FALSEFALSE
164/28/35FALSEFALSE
179/24/37FALSEFALSE
182/21/40FALSEFALSE
197/20/42FALSEFALSE
2012/16/44FALSEFALSE
215/15/47FALSEFALSE
2210/11/49FALSEFALSE
23FALSEFALSE
Sheet1
Cell Formulas
RangeFormula
P2:P23P2=AND(D3<>"",D3<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())))
Q2:Q23Q2=AND(D3<>"",D3>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),D2<TODAY())
D4:D22D4=D3+880
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D22Expression=AND(D3<>"",D3>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),D2<TODAY())textNO
D3:D22Expression=AND(D3<>"",D3<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())))textNO
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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
Back
Top