Excel Conditional Formatting Based on Date (9 Examples)

Example 1 – Using Built-In Date Rules

We’ll format the rows where the joining dates are within the past 7 days (Current date: 25-10-22).

Steps:

We stored the name of employees and their joining dates in the dataset.

Select the cells you want to apply the conditional formatting on (In our case, Range D5:D9).
Go to Home and select the Conditional Formatting option under the Style section.
Select the Highlight Cell Rules option and select the A Date Occurring option.

Conditional formatting with date occurring

A new window named A Date Occurring should appear.
Select the In the last 7 days option from the first drop-down menu.

Select the default color for highlighting cells.

Hit OK.

We want to highlight the dates of the last month.

Go to the A Date Occurring window as shown previously.
Choose the Last Month option from the drop-down list.

Click on the drop-down symbol for the highlighting color.
Choose the Custom Format option.

The Format Cells window appears.
Go to the Font tab.
Choose Bold as the desired Font style.

Move to the Fill tab.
Choose the desired color from the list.
Press the OK button.

Here’s the result.

In short in this section, we get options for yesterday, today, tomorrow, last week, this week, next week, last month, this month, and next month. We can avail of those options without using any other formula or technique.

Alternative Method:

Steps:

Click on the drop-down list of Conditional Formatting.
Click on the New Rule option.

The New Formatting Rule window appears.
Select Format only cells that contain.
Go to Edit the Rule Description section.
Choose the Dates Occurring option from the list.

You get a new drop-down field beside the previous section.
Click on the down arrow.

Choose the Last week option.
Click on the Format option.

Choose the desired Font and Fill color from the Format Cells window.
Press the OK button.

Go back to the previous window and see the Preview of the result.

Click on the OK button.

Example 2 – Highlight Dates Preceding the Current Date Using the NOW or TODAY Function

Using the TODAY function – It returns the current date.
Using the NOW function – It returns the current date with the current time.

We want to format the cells and highlight the date expired products based on the current date (25/10/22). We’ll highlight the cells with two colors: one for the date expired products and another one for products within the expiry date.

Steps:

Select the cells you want to apply the conditional formatting on (In our case, B5:D9).
Go to Home and select the Conditional Formatting option under the Style section.
Select the New Rule option from the drop-down menu.

A new window named New Formatting Rule should appear.
Select the Use a formula to determine which cells to format rule type.
Enter the following formula in the specified field.

=$D5<NOW()

Select the Format feature.

=$D5<NOW() checks whether the dates in Column D are less than the current date. If the date satisfies the conditions, then it formats the cell)

Select the desired format (see Example 1) and click OK.

Press the OK button.

We want to highlight cells with future dates.

Go to the New Formatting Rule window.
Put the following formula for products with a future date.

=$D5>Today()

We also formatted the highlighting color from the format section.

Press the OK button.

Example 3 – Using the WEEKDAY Function to Highlight Specific Days of a Week

We highlighted the weekends of the first two weeks of April 2021 in the calendar using the WEEKDAY function.

Steps:

Select the cells you want to apply the conditional formatting on (In our case, C7:L11).

Go to the New Formatting Rule window by following the steps of Example 2.
Select the Use a formula to determine which cells to format rule type.

Enter the following formula in the specified field.

=WEEKDAY(C$8,2)>5

Select the desired format by following the steps in Example 1.

Explanation:

=WEEKDAY(C$8,2)>5 only returns a TRUE value when the days are Saturday (6) and Sunday (7) and formats the cells accordingly.

Press the OK button.

Example 4 – Highlight Dates Within a Date Range Using the AND Rule in Conditional Formatting

We have formatted the rows where the joining dates are between two different dates. We will highlight the cells with the joining date between the start and the end date.

Steps:

Select the cells you want to apply the conditional formatting on (B8:D12).

Go to the New Formatting Rule window by following the steps of Example 2.
Select the Use a formula to determine which cells to format rule type.
Enter the condition/formula in the specified field

=AND($D8>=$C$4, $D8<=$C$5)

Select the desired format by following the steps from Example 1.

Explanation:

=AND($D13>=$C$4, $D13<=$C$6) checks whether the dates in Column D are greater than the C4 cell’s date and less than the C6 cell’s date. If the date satisfies the conditions, then it formats the cell).