Google Sheets Conditional Formatting

From HeadBackup
Jump to: navigation, search

Color the whole row based on dates in several columns

The following formulas will format an entire row if any of the values in columns C through G are dates that fall within the specified ranges.

Within 45 days

=or(and(IFERROR(ISNUMBER(DATEVALUE($C:$C)),FALSE),$C:$C>(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($D:$D)),FALSE),$D:$D>(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($E:$E)),FALSE),$E:$E>(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($F:$F)),FALSE),$F:$F>(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($G:$G)),FALSE),$G:$G>(today()-45)))

45 to 90 days

=or(and(IFERROR(ISNUMBER(DATEVALUE($C:$C)),FALSE),$C:$C>(today()-90),$C:$C<=(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($D:$D)),FALSE),$D:$D>(today()-90),$D:$D<=(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($E:$E)),FALSE),$E:$E>(today()-90),$E:$E<=(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($F:$F)),FALSE),$F:$F>(today()-90),$F:$F<=(today()-45)),
and(IFERROR(ISNUMBER(DATEVALUE($G:$G)),FALSE),$G:$G>(today()-90),$G:$G<=(today()-45)))

90 days or longer

=or(and(IFERROR(ISNUMBER(DATEVALUE($C:$C)),FALSE),$C:$C<=(today()-90)),
and(IFERROR(ISNUMBER(DATEVALUE($D:$D)),FALSE),$D:$D<=(today()-90)),
and(IFERROR(ISNUMBER(DATEVALUE($E:$E)),FALSE),$E:$E<=(today()-90)),
and(IFERROR(ISNUMBER(DATEVALUE($F:$F)),FALSE),$F:$F<=(today()-90)),
and(IFERROR(ISNUMBER(DATEVALUE($G:$G)),FALSE),$G:$G<=(today()-90)))