Use conditional formatting to identify dates earlier than today

During a Microsoft Excel training session a few week’s ago, I had a great question about using conditional formatting to highlight dates earlier than the current one, in other words, how can we highlight dates that occur in the past? This was a great question and so I thought I would publish a post this week about how to do this.

If you haven’t used conditional formatting in Excel before, be sure to check out my post here.

1. Open the workbook you wish to apply the formatting to
2. Highlight the column which contains the dates you wish to work with
3. From the Home tab select the Conditional Formatting button and choose New Rule
4. The New Formatting Rule dialog box will appear:

5. From the Select a Rule Type, choose Format only cells that contain
6. In the Edit the Rule Description section you need to tell Excel that any cell which contains a date which is less than today’s date, highlight it.

7. In the Format only cells with section, leave the first drop down menu set as Cell Value
8. For the next drop down menu change it to less than as shown below:

9. In the formula box type =TODAY()
10. This formula will look for a cell value that is less than “today”:

11. Now to specify a format you wish to apply, click the Format button
12. I have chosen to have the text change to Red and Bold

13. Click OK
14. Click OK again to complete the Conditional Formatting rule
15. Any dates which occur prior to today’s date will now appear in the formatting you selected:

Note: For the above example, today’s date is 20 February 2017.

If you have any questions about using conditional formatting, please comment below or feel free to contact me via the options provided or social media.