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)

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

2. Click on ‘Conditional Formatting’ and select ‘New Rule’. Once you click on this, it will bring up the ‘New Formatting Rule’ dialog box. 

3. In the ‘New Formatting Rule’ dialog box, under the “Select a Rule Type” options, select the “Format only cells that contain” option

4. 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”

5. 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

6. 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 

7. 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). 

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. 

8. 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. 

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.

Also read: How to Insert Date in Excel?

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.

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

2. Click on Conditional Formatting and select “New Rule”. Once you click on this it will bring up the New Formatting Rule dialog box. 

3. In the ‘New Formatting Rule’ dialog box, select “Format only cells that contain” option 

4. 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). 

5. In the next box you will enter the formula =today()

6. In the box after that you will enter the formula =today()-30

7. 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. 

8. 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
9. 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. 

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. 

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:

• Highlight Cell If the Value Exists in Another Column in Excel
• How to Highlight Blank Cells in Excel?
• How To Highlight Weekends In Excel?
• How to Add Days to Date in Excel
• How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
• How to Calculate the Number of Months Between Two Dates in Excel?
• Find the Last Monday of the Month Date in Excel (Easy Formula)
• Calculate Days Between Two Dates in Excel (Workdays/Weekends)
• How to Get the First Day Of The Month In Excel?