Mar 23 2021 05:07 PM
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?
Mar 23 2021 11:51 PM - edited Mar 23 2021 11:55 PM
=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.
Mar 24 2021 01:34 AM
SolutionIt 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_Sinha 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.
Mar 24 2021 02:13 AM
Mar 24 2021 03:12 AM
From this point of view what the difference between using DAY(TODAY()) or like and EDATE(TODAY(), n) ?
Mar 24 2021 09:27 PM
Mar 24 2021 01:34 AM
SolutionIt 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_Sinha 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.