Turning a cell yellow when the date is within 30 days of the value (date) of that cell

Question

Turning a cell yellow when the date is within 30 days of the value (date) of that cell

Patti 0

I have a column in Excel with the due date. I've already created a rule to turn the cell red if the due date goes past the cells value or due date. I would like to create another rule whereas the cell with the due date turns yellow when it's within 30 days of being due.

0 comments

1 answer

Your answer

Answer 1

Use another conditional formatting rule that tests whether the due date is within 30 days from today.

Assuming the due dates are in column B starting at B2:

Select the due date cells (for example, B2:B100).
On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
In Format values where this formula is true, enter:
```
   =AND(B2>=TODAY(), B2<=TODAY()+30)
```
Adjust B2 to match the first cell in the selected range.
Click Format, choose a yellow Fill color, and click OK.
Click OK again to save the rule.

Make sure this “yellow” rule is ordered below the “red when overdue” rule in the Conditional Formatting Rules Manager, so that overdue dates stay red and only upcoming (within 30 days) dates turn yellow.

References:

Use conditional formatting to highlight information in Excel