To apply conditional formatting to cells in Excel that have a date before today, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: Apply Conditional Formatting to Cells if Date is Before Today
Suppose we have the following dataset in Excel that shows the date that various people applied for some position:
This article is being written on 5/7/2023.
Suppose we would like to apply conditional formatting to any date that is before today.
To do so, we can highlight the cells in the range B2:B11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type =B2<TODAY() in the box, then click the Format button and choose a fill color to use.
Once we press OK, all of the cells in the range B2:B11 that have a date before today (5/7/2023) will be highlighted:
Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Apply Conditional Formatting to Overdue Dates
Excel: Apply Conditional Formatting if Cell is Between Two Dates
Excel: Apply Conditional Formatting Based on Multiple Conditions
how would you apply the formatting to the entire row, rather than just the one cell (i.e. in your example both column A & B would highlight if applicable)?
Hi Rich…
To apply conditional formatting to an entire row in Excel based on whether a date in one column is before today, follow these steps:
1. **Select the Entire Range**:
– Highlight the entire range of rows and columns to which you want to apply the conditional formatting. For example, select from `A1:B100` if your data spans columns A and B and rows 1 through 100.
2. **Open Conditional Formatting**:
– Go to the **Home** tab on the Ribbon.
– Click **Conditional Formatting** > **New Rule**.
3. **Use a Formula to Determine Which Cells to Format**:
– In the **New Formatting Rule** dialog box, select **Use a formula to determine which cells to format**.
4. **Enter the Formula**:
– Enter a formula that refers to the first row of the selection and checks if the date in column A is before today. For example:
“`excel
=$A1