Conditional formatting

Occasional Visitor

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

1 Reply


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


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


and red as fill color.