Home

Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1063369%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1063369%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20has%20various%20certification%20dates%20in%20it.%26nbsp%3B%20Some%20expire%20in%203%20years%20from%20the%20date%20entered%2C%20and%20some%20in%205%20years.%26nbsp%3B%20I%20do%20NOT%20have%20any%20idea%20how%20to%20set%20up%20the%20conditional%20formatting%20so%20I%20can%20have%20the%20separate%20cells%20color%20coded%20depending%20on%20how%20far%20out%20they%20are%20from%20the%20original%20date%20entered.%26nbsp%3B%20I%20would%20like%20to%20have%20orange%20when%20they%20are%2060-30%20days%20out%20from%20expiration%2C%20and%20red%20when%20they%20are%2029%20and%20less.%26nbsp%3B%20I%20have%20a%20total%20of%204%20different%20tests%20for%20about%20100%20people.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1063369%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1063390%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1063390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F486997%22%20target%3D%22_blank%22%3E%40Squrt17%3C%2FA%3E%26nbsp%3B%2C%20how%20the%20expiration%20is%20defined%20-%20do%20you%20have%20expiration%20date%2C%20or%20column%20where%20number%26nbsp%3B%20of%20years%20to%20expiration%20is%20defined%20(3%20or%205)%20or%20what%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1063400%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1063400%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20currently%20there%20is%20nothing%20defined%2C%20but%20I%20can%20certainly%20do%20that%20if%20necessary.%26nbsp%3B%20The%20only%20thing%20in%20the%20columns%20are%20the%20dates%20the%20individuals%20took%20the%20certification%20test.%26nbsp%3B%20I%20just%20know%20when%20they%20expire...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1063454%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1063454%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F486997%22%20target%3D%22_blank%22%3E%40Squrt17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20share%20with%20Excel%20that%20your%20knowledge.%3C%2FP%3E%0A%3CP%3EFor%20sample%20date%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20648px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F161498iB68FEDD3CE2A3E2D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20apply%20two%20conditional%20formatting%20rules%20with%20formulas%3C%2FP%3E%0A%3CP%3Efor%20the%20red%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D(TODAY()-EDATE(%24A2%2C%24B2*12)%26lt%3B%3D29)*(%24A2%26gt%3B1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eyellow%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D(TODAY()-EDATE(%24A2%2C%24B2*12)%26lt%3B%3D60)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20another%20range%2C%20instead%20of%20A2%20it%20shall%20be%20top%20left%20cell%20of%20your%20range%20(excluding%20headers)%2C%20B2%20is%20in%20the%20same%20row%20years%20expiration%20column%20and%20be%20careful%20with%20absolute%20and%20relative%20references.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Squrt17
New Contributor

I have a spreadsheet that has various certification dates in it.  Some expire in 3 years from the date entered, and some in 5 years.  I do NOT have any idea how to set up the conditional formatting so I can have the separate cells color coded depending on how far out they are from the original date entered.  I would like to have orange when they are 60-30 days out from expiration, and red when they are 29 and less.  I have a total of 4 different tests for about 100 people.  

3 Replies

@Squrt17 , how the expiration is defined - do you have expiration date, or column where number  of years to expiration is defined (3 or 5) or what?

 

@Sergei Baklan , currently there is nothing defined, but I can certainly do that if necessary.  The only thing in the columns are the dates the individuals took the certification test.  I just know when they expire...

@Squrt17 

You need to share with Excel that your knowledge.

For sample date like this

image.png

you may apply two conditional formatting rules with formulas

for the red

=(TODAY()-EDATE($A2,$B2*12)<=29)*($A2>1)

yellow:

=(TODAY()-EDATE($A2,$B2*12)<=60)

If another range, instead of A2 it shall be top left cell of your range (excluding headers), B2 is in the same row years expiration column and be careful with absolute and relative references.

Related Conversations
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies