Conditional formatting based on dates

New Contributor

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 from the current date.

I tried: =H2<(TODAY()+15)   but the results are inconsistent.  

2 Replies
best response confirmed by TrishAndShout (New Contributor)

@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:


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  thank you!  That is exactly what I did wrong! I need to change the relative reference.