my blank cells are fomatting as well

%3CLINGO-SUB%20id%3D%22lingo-sub-1578145%22%20slang%3D%22en-US%22%3Emy%20blank%20cells%20are%20fomatting%20as%20well%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578145%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22x-hidden-focus%22%3EI%20have%20a%20column%20of%20cells%20some%20contain%20dates%2C%20some%20are%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20cells%20with%20dates%20over%2011%20months%20old%20to%20format%20orange%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%26nbsp%3Bthe%20cells%20with%20dates%20over%20one%20year%20old%20to%20format%20red%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBlank%20cells%20should%20not%20change%20colour.%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EEverything%20I%20have%20found%20changes%20blank%20cells%20also%2C%20which%20does%20not%20work%20for%20my%20purposes%2C%20is%20there%20a%20way%20to%20get%20this%20result%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1578145%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578188%22%20slang%3D%22en-US%22%3ERe%3A%20my%20blank%20cells%20are%20fomatting%20as%20well%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F169468%22%20target%3D%22_blank%22%3E%40Charity%20K%20Gardiner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20the%20rule%20with%20the%20formula%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(%24A1%3CEDATE%3E1)%3C%2FEDATE%3E%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578223%22%20slang%3D%22en-US%22%3ERe%3A%20my%20blank%20cells%20are%20fomatting%20as%20well%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578223%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F169468%22%20target%3D%22_blank%22%3E%40Charity%20K%20Gardiner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20a%20new%20rule%20to%20format%20all%20cells%20that%20are%20equal%20to%20%3D%22%22%20with%20a%20custom%20format%20that%20has%20no%20color.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(alternatively%2C%20edit%20the%20existing%20rules%20to%20use%20formulas%20to%20determine%20which%20cells%20to%20format%2C%20but%20that%20is%20more%20work)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a column of cells some contain dates, some are blank.

 

I would like cells with dates over 11 months old to format orange

 

I would like the cells with dates over one year old to format red

 

Blank cells should not change colour.

Everything I have found changes blank cells also, which does not work for my purposes, is there a way to get this result?

2 Replies

@Charity K Gardiner 

You may use the rule with the formula like

=($A1<EDATE(TODAY(),-11))*($A1>1)

@Charity K Gardiner 

Add a new rule to format all cells that are equal to ="" with a custom format that has no color.

 

(alternatively, edit the existing rules to use formulas to determine which cells to format, but that is more work)