Apply Conditional Formatting for Dates Older Than Today in Excel

Let’s consider a shipment date of 10 items. The name of the items is in column B, their delivery time is in column C. So, our dataset is in the range of cells B5:C14. We get today’s date in cell D5 using the TODAY function.

Method 1 – Apply Simple Conditional Formatting to Highlight Dates Older Than Today

Steps:

• Select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group and choose the New Rules option.

• A small dialog box called the New Formatting Rule will appear.
• Select the Format only cells that contain option.
• In the second field, change the option from between to less than.

• In the next empty field, copy the following formula:

• Click the Format button.

• Another dialog box called Format Cells will appear.
• In the Fill tab, choose a cell color. We chose Orange, Accent 2, Lighter 40% color.
• Click OK to close the dialog box.

• Click OK to close the New Formatting Rule dialog box.

• You will see the dates which are older than today will get our desired cell color.

Read More: Highlight Row with Conditional Formatting Based On Date in Excel

Method 2 – Apply Conditional Formatting for Dates Older Than Today Using a Range

Steps:

• Select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group and choose the New Rules option.

• A small dialog box called the New Formatting Rule will appear.
• Select the Use a formula to determine which cells to format option.

• In the empty field below the text Format values where this formula is true, copy the following formula:

• Click the Format button.

• Choose a Fill color from the selector.
• Click OK.

• Click OK to close the New Formatting Rule dialog box.

• The dates which are older than 10 days from today are now highlighted.

Read More: Excel Conditional Formatting Based on Date

Method 3 – Highlight Dates Using Conditional Formatting Between Today and 30 Days Ago (Range of Dates)

Steps:

• Select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group and choose the New Rules option.

• A small dialog box called the New Formatting Rule will appear.
• Select the Format only cells that contain option.

• In the third empty box, copy the following:

• In the remaining empty box, insert the following formula:

• Click the Format button.

• Choose a color from the Fill tab.
• Click OK to close the Format cells dialog box.

• Click OK to close the New Formatting Rule dialog box.

• Excel will highlight dates that are between today and 30 days ago.

Read More: How to Change Cell Color Based on Date Using Excel Formula

Apply Conditional Formatting for Dates within 90 Days

Steps:

• Select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
• Select Highlight Cell Rules and choose the Between option.

• A small dialog box called Between will appear.
• In the first box, remove the default date and insert the following formula:

• In the next value box, copy the following formula:

• If you want your custom format, select the Custom Format option from the drop-down arrow and create your custom formatting.
• Click OK to close the dialog box.

• Excel will highlight dates no older than 90 days ago.

Read More: Excel Conditional Formatting for Dates within 30 Days

Perform Conditional Formatting for Dates Within 6 Months from Today

Steps:

• Select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
• Select Highlight Cell Rules and choose the Between option.

• A small dialog box called Between will appear.
• In the first value box, remove the default date and insert the following:

• In the second value box, insert the following:

• Customize the format if you want by clicking the third box.
• Click OK to close the dialog box.

• You will get the dates with the default highlighting color.

Use Conditional Formatting for Dates Within 1 Year

Steps:

• Select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
• Select Highlight Cell Rules and pick the Between option.

• A small dialog box called Between will appear.
• In the first value box, remove the default date and insert the following:

• In the second box, insert the following:

• Customize the format in the third box if you want.
• Click OK to apply the formatting.

• You will get the dates with the chosen highlighting color.

Download Practice Workbook

Download this workbook for practice. Modify the dates to check if the highlighting works.

Related Articles

• Conditional Formatting Based on Date in Another Cell in Excel
  
• Apply Conditional Formatting to Overdue Dates in Excel
  
• How to Apply Conditional Formatting to Each Row Individually
• How to Apply Conditional Formatting to Multiple Rows
• Conditional Formatting on Multiple Rows Independently in Excel
• How to Change Row Color Based on Text Value in Cell in Excel

<< Go Back to Conditional Formatting Based on Date | Conditional Formatting | Learn Excel