Highlighting days over 60 days old in Excel


When you have a list of dates in Excel, it is sometimes important to have the dates highlighted when they become significant.

We have a list of dates here that are billing dates. If we have not received payment on these invoices in 2 months, they will be highlighted. With these dates highlighted; action can be taken regarding these invoices.

This would also be effective with service dates on equipment so you would know when the next service was required. These would be highlighted after six or twelve months.


There are two formulas that we are using for this operation

The first one is

=Today()

this will always display the current date

The second one is

=Edate(date, Number of months)

So in this case it would be

We combine the two formulas and put it in Conditional formatting

  • Select the first date in the list
  • Click on Conditional Formatting on your Home tab
  • Select New Rule
  • Select use a formula to determine the cells format
  • Enter the above formula in the formula line
  • Click the Format button
  • •Select the Fill tab and select the fill colour
  • •Click OK and OK



The first cell in the list is conditionally formatted.

  • Click on this cell and click on the Formatting Paint Brush on the toolbar
  • Click on the letter above the column that you would like to conditionally format.

The trick is to format the first cell and use the format painter to conditionally format the entire column



Here is a link to a video on this topic