Manage vital deadlines using conditional formatting in Excel

You can use conditional formatting in Excel to highlight dates based on how close they are to the current date. This is a good way of staying on top of deadlines.

This example marks anything more than five days old with red, anything between five days before and after the current date as yellow and anything more than five days in advance.

Select the cells with the dates you want to highlight, click on the Format menu and then Conditional Formatting. Select ‘Cell Value is’ and then ‘less than’. Type =TODAY()-5. Click on the Format button, Patterns and select red as these are overdue dates. Click on the Add button.

Select ‘Cell Value Is’ and then ‘between’. Type =TODAY()-5 in the first cell and =TODAY()+5 in the second. Use the formatting button to select yellow to show these due dates. Click on the Add button.

Select ‘Cell Value Is’ and then ‘greater than’. Type =TODAY()+5 in the text box. Use the format button to select green to show dates yet to arrive. Click on OK to apply the formatting.

Richard Whitten