Conditional Formatting by Date

Question

I am tracking medical cases and their start dates. All of the dates for each separate case are in column E. I would like a way to track the timeframe of each separate case by their start date. Is there a way to make the bar turn yellow when the case is 5 months old from original start date and again turn red when the case hits the 6 month mark. I would like to format this only to column E.

Answer 1

Thank you so much! This was exactly what I was needing!!

Answer 2

I'll assume that the dates begin in E2.

Select E2:E1000 or however far down you want. E2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND(E2<>"", E2<=EDATE(TODAY(), -5))

Click Format...
Activate the Fill tab.
Select yellow as fill color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=AND(E2<>"", E2<=EDATE(TODAY(), -6))

and red as fill color.