Highlight Dates Before Today or Within a Date Range in Excel Worksheets

by Avantix Learning Team | Updated November 21, 2021

Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows)

You can use conditional formatting in Excel to highlight cells containing dates before today or within a date range before the current date. In a worksheet, you can use conditional formatting to highlight selected cells by filling them with a color based on rules or conditions. This type of formatting is helpful if you want to highlight past due dates such as invoices that are 30, 60 or 90 days overdue.

When you apply conditional formatting, you can apply more than one rule to a range of cells.

In this article, we’ll review 3 ways to highlight dates before today using conditional formatting:

  1. Use built-in conditional formatting
  2. Create a conditional formatting rule based on cell contents
  3. Create a conditional formatting rule using a formula

Recommended article: How to Delete Blank Rows in Excel (5 Easy Ways with Shortcuts)

Do you want to learn more about Excel? Check out our virtual classroom or in-person Excel courses >

Dates must be entered or returned as valid dates in Excel in order to apply conditional formatting. Dates are affected by your regional settings and date / time settings in your Control Panel on your device.

For example, if your regional settings are set to U.S., dates are typically entered as mm/dd/yy (month / day / year) unless the date / time settings have been changed. For example, November 14, 2021 may be entered at 11/14/21 if your device’s regional settings are set to U.S (you can also enter dates with dashes such as mm-dd-yy). To test your device to see how dates are recognized, press Ctrl + ; (semi-colon) in a cell in an Excel worksheet to enter the current date in the cell. Dates can then be formatted to appear in a different ways.

The current date can also be entered in a cell using the TODAY function such as =TODAY() or the NOW function such as =NOW(). The NOW function enters the current date and time whereas the TODAY function only enters the current date. They are dynamic functions so will always display the current date.

1. Highlighting dates before today using built-in conditional formatting

To highlight dates before today using a built-in conditional formatting rule:

  1. Select the cells containing dates to which you want to apply conditional formatting.
  2. Click the Home tab in the Ribbon.
  3. Click Conditional Formatting in the Styles group. A menu appears to the right.
  4. Select Highlight Cell Rules. A menu appears.
  5. From the drop-down menu, choose Dates Occurring. A dialog box appears.
  6. From the first drop-down menu, select an option such as Yesterday, In the last 7 days, Last Week, or Last Month.
  7. In the second drop-down menu beside With, select Custom Format. A dialog box appears.
  8. Click the Fill tab and then click the desired fill color. You can also click the Font tab and select a font color or another format such as bold.
  9. Click OK twice.

The Date Occurring dialog box appears as follows with In the last 7 days selected (this option would include the current date):

Date Occurring built-in dialog box in Excel to highlight cells containing dates before today using conditional formatting.

In the Format Cells dialog box, you can select a fill as the conditional format:

Format cells dialog box in Excel to highlight dates before today using conditional formatting.

2. Highlighting dates before today by creating a conditional formatting rule based on cell contents

You can also apply your own formatting rules based on cell contents. This method gives you more control over how cells are formatted and allows you to do things you wouldn’t be able to do with the built-in formats.

To highlight dates before today by creating a conditional formatting rule based on cell contents:

  1. Select the cells containing dates to which you want to apply conditional formatting.
  2. Click the Home tab in the Ribbon.
  3. Click Conditional Formatting in the Styles group. A drop-down menu appears.
  4. Select New Rule. A dialog box appears.
  5. Select Format only cells that contain.
  6. Below Format only cells with, ensure Cell Value is selected.
  7. From the second drop-down menu, select less than.
  8. In the text box, enter =TODAY().
  9. Click Format. A dialog box appears.
  10. Click the Fill tab and then click the desired fill color. You can also click the Font tab and select a font color or another format such as bold.
  11. Click OK twice.

In the Edit Formatting Rule dialog box below, we entered a rule to highlight the selected cells if the cell value is less than =TODAY():

Edit formatting rule dialog box to highlight cells containing a date before today.

To highlight cells containing a range of dates between today and a date in the past (not including today):

  1. Select the cells containing dates to which you want to apply conditional formatting.
  2. Click the Home tab in the Ribbon.
  3. Click Conditional Formatting in the Styles group. A drop-down menu appears.
  4. Select New Rule from the drop-down menu.
  5. Select Format only cells that contain.
  6. Below Format only cells with, ensure Cell Value is selected.
  7. From the second drop-down menu, select Between.
  8. In the first text box, enter =TODAY()-1.
  9. In the second text box, enter another date or formula. For example, if you are looking for dates in the last 90 days before the current date, enter =TODAY()-90. For 30 days overdue, enter =TODAY()-30.
  10. Click Format. A dialog box appears.
  11. Click the Fill tab and then click the desired fill color. You can also click the Font tab and select a font color or another format such as bold.
  12. Click OK twice.

In the Edit Formatting Rule dialog box below, we entered a rule to highlight the selected cells if the cell value is between =TODAY()-1 and =TODAY()-90 (within 90 days before the current date):

Edit formatting rule dialog box in excel to highlight dates before today using conditional formatting and format cells that contain a range of dates.

3. Highlighting dates before today by creating a conditional formatting rule using a formula

You can use a variety of formulas in conditional formatting to change cell formats based on a formula.

When you are writing formulas in conditional formatting rules, context is very important. Write the formula as if you are in the active cell (often the first cell) and Excel, by default, uses relative referencing to copy the formula to the other cells.

To highlight dates before today by creating a conditional formatting rule using a formula:

  1. Select the cells in a column containing dates to which you want to apply conditional formatting.
  2. Click the Home tab in the Ribbon.
  3. Click Conditional Formatting in the Styles group. A drop-down menu appears.
  4. Select New Rule from the drop-down menu.
  5. Click Use a formula to determine which cells to format.
  6. Enter the formula (starting with =) in the formula box (the formula references the active cell which is usually the first cell and the conditional formatting tool will adjust it for the other cells if relative referencing is used). For example, enter =A2<TODAY() if A2 is the first in the range of selected cells containing a date.
  7. Click Format. A dialog box appears.
  8. Click the Fill tab and then click the desired fill color. You can also click the Font tab and select a font color or another format such as bold.
  9. Click OK twice.

In the Edit Formatting Rule dialog box below, we entered a formula rule of =A2<TODAY() to highlight the selected cells if the cell value is before today:

Edit formatting rule dialog box in Excel to format dates before today using a formula.

You can also use absolute and mixed referencing in conditional formatting rules.

If you want to highlight selected cells in a row with conditional formatting, select the range of cells across multiple columns and use a mixed reference with a dollar sign ($) in front of the column reference in the formula. For example, enter =$A2<TODAY() to highlight selected cells in a row before today.

If you are referring to cells in a column in an Excel table (such as a Date column), you can write a formula that refers to a field in the table. An Excel table is typically created using the Insert tab in the Ribbon and clicking Table or by pressing Ctrl + T.

To highlight cells containing a range of dates between today and a date in the past (not including today) in cells in a column in an Excel table:

  1. Select the cells containing dates in a table column to which you want to apply conditional formatting. You can click the first data cell in the column and then press Ctrl + Shift + down arrow to select the remaining cells in the table column.
  2. Click the Home tab in the Ribbon.
  3. Click Conditional Formatting in the Styles group. A drop-down menu appears.
  4. Select New Rule from the drop-down menu.
  5. Click Use a formula to determine which cells to format.
  6. Enter the formula (starting with =) in the formula box. For example, enter =[@Date]<TODAY(). In this case, Date is the field or the column header and @ is a specifier that refers to the current record.
  7. Click Format. A dialog box appears.
  8. Click the Fill tab and then click the desired fill color. You can also click the Font tab and select a font color or another format such as bold.
  9. Click OK twice.

If you apply conditional formatting to cells in a column in a table, when you add new records to the table, the conditional formatting will expand to include the new rows.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Use Flash Fill in Excel (4 Ways with Shortcuts)

How to Quickly Delete Blank Rows in Excel (5 Ways)

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

How to Replace Blank Cells in Excel with a Value from the Cell Above

10 Excel Flash Fill Examples (Extract, Combine, Clean and Format Data with Flash Fill)

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom onsite training or an online course on a date that's convenient for you.

Copyright 2021 Avantix® Learning

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
How to Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways)
Article Name
How to Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways)
Description
You can use conditional formatting in Excel to highlight cells containing dates before today or within a date range before the current date. In a worksheet, you can use conditional formatting to highlight selected cells by filling them with a color based on rules or conditions. This type of formatting is helpful if you want to highlight past due dates such as invoices that are 30, 60 or 90 days overdue.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This