Conditional formatting based on dates

I'm sure I used to know how to do this, but now that I need it, I can't figure it out! I want to format the cells in column B to a different fill color if the date in column H is less than 15 days f...

Formulas and Functions

mtarler
Jan 28, 2021
TrishAndShout without the actual sheet it is hard to know for sure but the basic formula looks right. The biggest mistake is which cell to reference in the formula so in your example the formula is:
=H2<(TODAY()+15)
If the "Applies To" range is B:B or B1:B50 then for B1 it will look at H2 (instead of H1) and then for B2 it will look at H3 (instead of H2). So you need to make sure that the relative cell is relative to the upper left most cell of the "Applies To" range(s).
Hope that helps but if not please attach a sample sheet for us to see more.

mtarler

Silver Contributor

Jan 28, 2021

TrishAndShout without the actual sheet it is hard to know for sure but the basic formula looks right. The biggest mistake is which cell to reference in the formula so in your example the formula is:

=H2<(TODAY()+15)

If the "Applies To" range is B:B or B1:B50 then for B1 it will look at H2 (instead of H1) and then for B2 it will look at H3 (instead of H2). So you need to make sure that the relative cell is relative to the upper left most cell of the "Applies To" range(s).

Hope that helps but if not please attach a sample sheet for us to see more.

TrishAndShout

Copper Contributor

Jan 29, 2021

mtarler thank you! That is exactly what I did wrong! I need to change the relative reference.

Forum Discussion

Conditional formatting based on dates