SOLVED

When does excel check the conditions of conditional formatting?

%3CLINGO-SUB%20id%3D%22lingo-sub-2231352%22%20slang%3D%22en-US%22%3EWhen%20does%20excel%20check%20the%20conditions%20of%20conditional%20formatting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2231352%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20created%20conditional%20formatting%20for%20tracking%20currency%20of%20certifications%20of%20team%20members.%20I%20just%20want%20the%20fill%20to%20be%20green%2C%20yellow%2C%20or%20red%20based%20on%20when%20the%20certification%20occurred.%20My%20formulas%20are%20a%20little%20janky%2C%20but%20they%20seem%20to%20be%20working.%20these%20are%20the%20formulas%20I'm%20using.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrue%20if%20date%20is%20older%20or%20equal%20to%205%20years%20old%2C%20fill%20red%3C%2FP%3E%3CP%3E%3D%24E3%26lt%3B%3DTODAY()-1825%3C%2FP%3E%3CP%3ETrue%20if%20date%20is%20older%20or%20equal%20to%204%20AND%20newer%20than%205%20years%20old%2C%20fill%20yellow%3C%2FP%3E%3CP%3E%3DAND(%24E3%26lt%3B%3DTODAY()-1460%2C%20%24E3%26gt%3BTODAY()-1825%3C%2FP%3E%3CP%3ETrue%20if%20date%20is%20newer%20than%204%20years%20old%2C%20fill%20green%3C%2FP%3E%3CP%3E%3D%24E3%26gt%3BTODAY()-1460%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20when%20does%20Excel%20check%20the%20conditions%20and%20apply%20formatting%3F%20Is%20it%20just%20when%20the%20date%20is%20put%20into%20the%20cell%2C%20or%20does%20it%20also%20check%20when%20the%20document%20is%20opened%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2231352%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2231744%22%20slang%3D%22en-US%22%3ERe%3A%20When%20does%20excel%20check%20the%20conditions%20of%20conditional%20formatting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2231744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1006636%22%20target%3D%22_blank%22%3E%40mchri913%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3E%3CSTRONG%3EFor%201st%20Condition%3A%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DOR(%24E3%20%3CTODAY%3E%3C%2FTODAY%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2.%26nbsp%3B%3CSTRONG%3EFor%202nd%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DOR(%24E3%20%26lt%3B%3DDATE(YEAR(TODAY())%2B4%2C%20MONTH(TODAY())%2C%20DAY(TODAY()))%2C%24E3%26gt%3B%20DATE(YEAR(TODAY())%2B5%2C%20MONTH(TODAY())%2C%20DAY(TODAY())))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%203.%20%3CSTRONG%3EFor%20last%20one%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%24E3%20%26gt%3B%20DATE(YEAR(TODAY())%2B4%2C%20MONTH(TODAY())%2C%20DAY(TODAY()))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%26nbsp%3B%3C%2FSTRONG%3E%3CEM%3EYou%20may%20adjust%20cell%20references%2C%20criterion%20%26amp%3B%20cell%20colors%20as%20needed.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2231881%22%20slang%3D%22en-US%22%3ERe%3A%20When%20does%20excel%20check%20the%20conditions%20of%20conditional%20formatting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2231881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1006636%22%20target%3D%22_blank%22%3E%40mchri913%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20checks%20every%20time%20when%20worksheet%20is%20re-calculated.%20Thus%2C%20if%20calculation%20option%20is%20not%20Manual%20but%20Automatic%2C%20on%20workbook%20opening%20and%20each%20time%20you%20enter%20something.%3C%2FP%3E%0A%3CP%3EIn%20addition%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%20suggestion%20I'd%20recommend%20to%20use%20EDATE()%20function.%20For%20example%2C%20date%205%20years%20ago%20could%20be%20calculated%20as%20%3DEDATE(TODAY()%2C%20-5*12).%20That's%20more%20suitable%20from%20maintenance%20point%20of%20view.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I've created conditional formatting for tracking currency of certifications of team members. I just want the fill to be green, yellow, or red based on when the certification occurred. My formulas are a little janky, but they seem to be working. these are the formulas I'm using.

 

True if date is older or equal to 5 years old, fill red

=$E3<=TODAY()-1825

True if date is older or equal to 4 AND newer than 5 years old, fill yellow

=AND($E3<=TODAY()-1460, $E3>TODAY()-1825

True if date is newer than 4 years old, fill green

=$E3>TODAY()-1460

 

My question is, when does Excel check the conditions and apply formatting? Is it just when the date is put into the cell, or does it also check when the document is opened?

5 Replies

@mchri913 

 

  1. For 1st Condition:

 

=OR($E3 <TODAY( ),$E3=DATE(YEAR(TODAY())+5, MONTH(TODAY()), DAY(TODAY())))

 

     2. For 2nd: 

 

=OR($E3 <=DATE(YEAR(TODAY())+4, MONTH(TODAY()), DAY(TODAY())),$E3> DATE(YEAR(TODAY())+5, MONTH(TODAY()), DAY(TODAY())))

 

    3. For last one:

 

=$E3 > DATE(YEAR(TODAY())+4, MONTH(TODAY()), DAY(TODAY()))

 

 

N.B. You may adjust cell references, criterion & cell colors as needed.

best response confirmed by mchri913 (Occasional Visitor)
Solution

@mchri913 

It checks every time when worksheet is re-calculated. Thus, if calculation option is not Manual but Automatic, on workbook opening and each time you enter something.

In addition to @Rajesh-S  suggestion I'd recommend to use EDATE() function. For example, date 5 years ago could be calculated as =EDATE(TODAY(), -5*12). That's more suitable from maintenance point of view.

Since TODAY has been used with EDATE then it doesn't makes any sense,, because I do believe that TODAY is dynamic & EDATE is best suitable with Static date !!

@Rajesh-S 

From this point of view what the difference between using DAY(TODAY()) or like and EDATE(TODAY(), n) ?

You are unable to catch the abstract of my reply,, I was supposed to say what is the use of using TODAY with EDATE,, since examples show by me has the same result,, working with TODAY,,, !!

Don't you think that this one DATE(YEAR(TODAY())+4 is much easier & straight forward rather then EDATE(TODAY(), -5*12) !!