Sep 27 2022 03:35 AM
I have three columns (Status, Start date, End date)
If the end date hasn’t been reached, I would like the status column to be green
If the end date is approaching in the next 5 days, I would like the status column to go yellow/ orange
If the end date has passed, I would like the status column to go red
Sep 27 2022 03:55 AM
Let's say start date is in B2:B100, end date in C2:C100 and status in D2:D100.
Select D2:D100. D2 should be the active cell in the selection.
Select green as fill color. This will be the default.
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
=$C2<=TODAY()+5
Click Format...
Activate the Fill tab of the Format Cells dialog.
Select yellow or orange.
Click OK, then click OK again.
Repeat these steps but with the formula
=$C2<TODAY()
and red as fill color.