Forum Discussion
Solved
Conditional Formatting with dates and highlights
I am writing a workbook and need a conditional formula that highlights due dates the month before they are due in yellow and stays yellow until the due date. Then changes to a pink color if overdue. I have a cell showing the current date, is there a way to use that cell in the formula?
- you don't need the cell with the current date as that can just be in the formula.
Let's say the cells you want to check/highlight are D5:D100, highlight those cells.
Goto conditional formatting and choose to add a new custom using a formula.
The "Applies To" range will automatically be D5:D100 (the highlighted range)
write the formula as if you were writing it for the upper left cell in that range (D5) so:
=EDATE(D5, -1)<TODAY()
Change the formatting to be fill Yellow
now repeat with
=D5<TODAY()
and formatting pink (and make sure this rule take precedence over the other
- you don't need the cell with the current date as that can just be in the formula.
Let's say the cells you want to check/highlight are D5:D100, highlight those cells.
Goto conditional formatting and choose to add a new custom using a formula.
The "Applies To" range will automatically be D5:D100 (the highlighted range)
write the formula as if you were writing it for the upper left cell in that range (D5) so:
=EDATE(D5, -1)<TODAY()
Change the formatting to be fill Yellow
now repeat with
=D5<TODAY()
and formatting pink (and make sure this rule take precedence over the other
