How to Highlight Dates Before Today in Excel?

There may be instances in which you will need to call attention to dates before today’s date.

This could be to capture payments that were due but were not received or invoices that may be past due.

In this tutorial, I will demonstrate to you how to highlight dates before today’s date in Excel. I will also show you how to highlight dates before today but not older than 30 or 60 or 90 days.

Highlight Dates Before Today Using Conditional Formatting

Below I have a data set where I have the dates in column A and I want to highlight those dates that have already elapsed (i.e., dates before today)

Dates dataset

Below are the steps to do this:

  1. Select the cells that have the dates
  2. Click on the Home tab in the ribbon and then locate the ‘Conditional Formatting’ option
Click on conditional formatting
  1. Click on ‘Conditional Formatting’ and select ‘New Rule’. Once you click on this, it will bring up the ‘New Formatting Rule’ dialog box. 
click on new rule
  1. In the ‘New Formatting Rule’ dialog box, under the “Select a Rule Type” options, select the “Format only cells that contain” option
Select Format Only cells that contain
  1. Next, you will want to look below this to the “Edit the Rule Description” section. From left to right the first box is prepopulated with “Cell Value.” Leave that the way it is. The next box will be prepopulated to display “between.” Click on the drop-down arrow next to that and select “less than”
select less than
  1. In the box just to the right of where you selected less than from the drop-down menu, you will be typing in a formula. That formula is =TODAY(). This will use today’s date which is taken from your computer’s system settings
Enter today formula
  1. Down below, you will notice that the word “Preview” is in bold, and the large box shows “No Format Set.” Here you need to click on the ‘Format’ button 
Click the Format button
  1. This will bring up the Format Cells dialog box. From here you will be able to select the Font Style (Regular, Italic, Bold, or Bold italic), Underline (none, single, double), enable Strikethrough on or off, and, the Color of the date in the cell (Automatic is the default, click on the arrow to the right to bring up the available colors). 
select the formatting

Once you have made your selection(s) click on the OK button, then click the OK button again on the “New Formatting Rule” dialog box. 

  1. Once you have completed these steps you will have your cells formatted based on dates before today’s date. I selected the dates to be red and bold. 
Dates before today have been highlighted

Once you have applied the Conditional Formatting rule to a range of cells, when you change the value in the cell, it would automatically update. also, since we have used the Today function, which always takes the current date based on the system setting, if you open this Excel file on a future date, it would highlight the cells accordingly based on the current date.

Highlight Dates Between Today and 30/60/90 Days Ago

There may come a time when you need to break down the data to show what has happened in the last 30 days.

In this section, I will show you how to format cells to highlight the cells between today’s date and the previous 30 days (or the previous 60/90 days). 

Below our data set where I have the dates in column A comma and I want to highlight all the dates that occurred between today and the past 30 days.

Dates dataset

Below are the steps to do this:

  1. Select the cells that have the dates
  2. Locate Conditional Formatting on the Home tab and click on it
Click on conditional formatting
  1. Click on Conditional Formatting and select “New Rule”. Once you click on this it will bring up the New Formatting Rule dialog box. 
click on the new rule option
  1. In the ‘New Formatting Rule’ dialog box, select “Format only cells that contain” option 
Click on format cells that contain
  1. In the ‘Edit the Rule Description’ section under ‘Format only cells with:’, select the ‘Cell Value’ and ‘between’ options from the drop-down (if not selected by default already). 
Select between
  1. In the next box you will enter the formula =today()
Enter TODAY
  1. In the box after that you will enter the formula =today()-30
Enter Today()-30 formula
  1. Down below you will notice that the word “Preview” is in bold and the large box shows “No Format Set.” Here you will click on the format button to the right of that box. 
Click on Format
  1. This will bring up the Format Cells dialog box. Specify the format that you want to apply on the cells that fulfill the criteria. I will go with Red color and Bold font format
  2. Once you have made your selection(s) click on the OK button, then click the OK button again on the “New Formatting Rule” dialog box. 

Once you have completed these steps you will see that only the dates that occurred within the past 30 days while those that are outside of the past 30 days are still with the standard weight black font. 

Dates between today and 30 days are highlighted

Note: If you only wanted to capture things that have happened in the past (for instance not count today’s date but only yesterday and before) in step 5 the formula you would enter would be =today()-1. This would give you the result of only those dates that occurred yesterday and within the last 30 days. 

Use TODAY -1 to exclude the current date

Protip: If you also needed to capture dates that were 31-60 days, or 61-90 days, etc. you will just need to repeat these steps but in the first box use the formula =today()-minimum number of days.

In the second box put =today()-maximum number of days. You will also want to select a different format to make sure that the rule stands out from the others that you have created. 

In this article, I showed you how to highlight dates before today in Excel. I also covered how to highlight dates that are older that today but not 30 or 60 days old.

Other articles you may also like: