Excel Conditional Formatting Based on Date (9 Examples)

Conditional formatting has been used in Excel for quite a long time. But what is conditional formatting, and how can it benefit you? The process of putting up the conditions that decide the formatting applied to a column or a row is known as conditional formatting. It helps to present the data in a more organized manner. In this article, we will discuss different ways for applying Excel conditional formatting based on date.

If you want to learn about conditional formatting in general, then check out this article.

Download Practice Workbook

Download the following practice workbook to exercise while you are reading this article.

9 Examples of Conditional Formatting Based on Date in Excel

We will discuss 9 examples of conditional formatting based on the date in the following sections.

1. Using Built-In Date Rules

There are some built-in date rules in the Conditional Formatting option which provides 10 different conditions to format selected cells based on the current date. In this example, I have used one of these ten rules to 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 my case, Range D5:D9).
• Go to Home and select the Conditional Formatting option under the Style section.
• Select the Highlight Cell Rules option first and then select the A Date Occurring option from there.

• 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 of highlighting cells.

• Finally, press the OK buttons and look at the dataset.

The condition will be handled automatically by Excel. We can choose the other nine built-in options as per our needs.

• Now, 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.

• Then, 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.

• Again, move to the Fill tab.
• Choose the desired color from the list.
• Then, press the OK button.

• Look at the dataset.

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:

There is an alternative method of built-in date option in Excel. Have a look at the below section.

📌 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 the option.
• Then go to Edit the Rule Description section.
• Choose the Dates Occurring option from the list.

• After that, we see a new drop-down field beside the previous section.
• Click on the down arrow.

We get a similar list of the 1st method shown in the upper section. It also contains the same 10-date options.

• Now, choose the Last week option.
• Then, click on the Format option.

• We choose the desired Font and Fill color from the appeared Format Cells window.
• Press the OK button.

• We will go back to the previous window and see the Preview of the result.

• Finally, click on the OK button.

We can see the cells containing the dates of the last week have been changed.

Read More: Conditional Formatting Based On Another Cell Date in Excel

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

This example shows how you can apply conditional formatting in selected cells based on the current date. We will be able to detect past and future dates in this example. There are two popular ways of getting the current date in MS Excel

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

Here, we want to format the cells and highlighted the date expired products based on the current date (25/10/22). I have used the NOW function in this example but you can use the TODAY function instead of NOW as well. It will give the same result. We 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 my 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 Use a formula to determine which cells to format rule type.
• Enter the formula in the specified field.

• After that, select the Format feature.

Explanation: The dollar sign ($) is known as the Absolute Symbol. It makes the cell references absolute and doesn’t allow any changes. You can lock a cell by selecting the cell and pressing the F4 button.

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

• We will select the desired format (see Example 1) and click OK.
• Go back to the previous window and have look at the Preview section.

• Again, press the OK button and look at the dataset.

We can see the products with dates expired or past dates have been rowing color changed. Now, we want to highlight cells with future dates.

• Again, go to the New Formatting Rule window.
• Put the following formula for products with a future date.

• We also formatted the highlighting color from the format section.

• Finally, press the OK button.

We can see the products with past dates and future dates have been marked with different colors.

Read More: Apply Conditional Formatting for Dates Older Than Today in Excel

3. Use of WEEKDAY Function to Highlight Specific Days of a Week

The WEEKDAY function returns a number from 1 to 7 identifying the day of the week of a date.

This example introduces you to the WEEKDAY function and shows how you can use it to highlight weekends in a calendar. Here, I have 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 my case, C7:L11).

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

• Enter the formula in the specified field.

• Then, select the desired format by following the steps in Example 1.

• Finally, press the OK button and look at the dataset.

It will format the selected cells according to the condition and selected format.

Read More: How to Highlight Row with Conditional Formatting Based on Date in Excel

Similar Readings

• Pivot Table Conditional Formatting Based on Another Column (8 Easy Ways)
• How to Make Negative Numbers Red in Excel (4 Easy Ways)
• Excel Alternating Row Color with Conditional Formatting [Video]
• How to Apply Conditional Formatting with INDEX-MATCH in Excel
• How to Do Conditional Formatting Highlight Row Based On Date

4. Highlight Dates Within a Date-Range Using AND Rule in Conditional Formatting

This example shows how you can apply conditional formatting in selected cells within a certain range of dates.

Here, I 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 (In my case, B8:D12).

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

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

• Finally, press the OK button.

It will format the selected cells according to the condition and selected format.

One more thing needs to add that, we applied the condition formatting based on another cell.

Alternate Method:

There is an alternate method in conditional; formatting to highlight cells within a range.

• First, select Range B8:D12.
• Choose Highlight Cells Rules from the Conditional Formatting drop-down.
• Click on the Between option from the list.

• As a result, dialog box will appear named Between.
• Put the cell reference of the start date on the box marked as 1 and the end date on the box marked as 2.

• Finally, press the OK button.

The difference between the two methods is that 1st method modifies the color of the whole row based on the condition. But the alternative method is applicable to the cells only.

Read More: How to Highlight Row Using Conditional Formatting (9 Methods)

5. Highlight Holidays with MATCH or COUNTIF Function in Conditional Formatting

In this section, we will show how to use the MATCH or COUNTIF function to highlight a column that meets a date criteria with the desired color.

📌 Steps:

• First, we add the list of holidays of April 2021 to the dataset.

• Now, select Range C7:L11.

• Follow the steps of Example 2 and enter the following formula on the marked field.

• After that, choose the desired color from the Format section.

Here, we applied the formula based on the MATCH function.

• Then, press the OK button.

However, we can also use the formula based on the COUNTIF function and that will perform the same operation.

Read More: How to Apply Excel Conditional Formatting with Formula for Multiple Conditions

Similar Readings

• How to Highlight Highest Value in Excel (3 Quick Ways)
• How to Highlight Lowest Value in Excel (11 Easy Ways)
• Excel Formula to Color Cell If It Has Specific Value
• Conditional Formatting on Multiple Rows Independently in Excel
• If Cell Color Is Red Then Execute Different Functions in Excel

6. Excel Conditional Formatting Based on Date Older Than 1 Year

In this example, we want to highlight the dates that are older than 1 year. Assuming that we have a dataset of people who joined a company. We will apply the conditional formatting based on the formula to highlight the dates older than 1 year in Excel.

📌 Steps:

• First, select Range D5:D9, which contains dates only.
• Select the Less Than option from the Highlight Cells Rules section.

• The Less Than window appears.
• Put the following formula based on the TODAY function in the marked section.

• Finally, press the OK button.

Read More: Excel Conditional Formatting for Dates within 30 Days

7. Excel Conditional Formatting Based on Date Less Than 6 Months from Today

In this example, we will find out the cells with a date less than 6 months from today. For that, we will use the TODAY function here.

📌 Steps:

• Select the Range D5:D9.

• Follow the steps of Example 2.
• Then insert the following formula on the box marked as 2.

• After that, we define the format of highlighted cells as shown in Example 1.

• Finally, press the OK button.

We can see the dates less than 6 months are highlighted with the desired color.

Read More: Apply Conditional Formatting to Overdue Dates in Excel (3 Ways)

8. Excel Conditional Formatting Based on Date Past Due of 15 Days

In this section, we want to highlight dates with 15 days due from the present day. Have a look at the below section for details.

📌 Steps:

• First, select the cells of the Joining Date column.

• Follow the steps of Example 2 and go to the New Formatting Rule section.
• Now, put the following formula on the box marked as 2.

• Then, choose the highlighting color from the Format

• At last, press the OK button.

We can change the due day in the formula.

Read More: Excel Conditional Formatting Dates

9. Conditional Formatting Based on Date in Another Column

In this section, we will apply conditional formatting on the Actual Delivery Date column based on the Expected Delivery Date.

📌 Steps:

• First, Select the Range B5:C9.

• Now, go to the New Formatting Rule section as shown in Example 2.
• Then put the following formula on the marked section.

• Choose the desired cell color from the Format feature.

• Again, press the OK button.

So, conditional formatting has been applied based on another column.

Read More: Conditional Formatting Entire Column Based on Another Column in Excel

Conclusion

In this article, we described conditional formatting based on the date in different situations in Excel, and I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.

Related Articles

• How to Remove Conditional Formatting but Keep the Format in Excel
• VBA Conditional Formatting Based on Another Cell Value in Excel
• Excel Conditional Formatting If a Cell Is Greater Than Another One
• How to Find Highest Value in Excel Column (4 Methods)
• How to Change Text Color with Formula in Excel (2 Methods)
• Conditional Formatting on Text that Contains Multiple Words in Excel