Conditional Formatting Overdue/Upcoming Dates in Excel

Excel Conditional Formatting for Dates
Excel Conditional Formatting for Dates. © Ted French

Adding conditional formatting to a cell in Excel allows you to apply different formatting options, such as color, to a cell or cells that contain dates.

Excel has several built in options that allow you to easily add formatting so that a date that has passed such as overdue bills is easily spotted.

Similarly, a date in the future can also be highlighted using different background and/or font colors.

To find dates that do not match the pre-set choices, you can add new conditional formatting rules that use date formulas to find the information you are after.

Example: Adding Conditional Formatting to a Date in Excel

  1. Click on cell C1 to make it the active cell.
  2. Click on the Home tab.
  3. Click on the Conditional Formatting icon on the ribbon to open the drop down menu.
  4. Choose Highlight Cell Rules > A Date Occurring... to open the conditional formatting dialog box.
  5. Click on the down arrow of the date options drop down menu (left side) to open the list.
  6. Select In the last 7 days from the list.
  7. Click on the down arrow of the format options drop down menu (right side) to open the list.
  8. Select Light Red Fill with Dark Red Text from the list.
  9. Click OK to accept the changes and close the dialog box.
  10. Type yesterday's date in cell C1 and press the ENTER key on the keyboard.
  11. Cell C1 should be formatted with a light red background color and dark red text since yesterday's date occurred within the last seven days.