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.

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.

Conditional formatting with default date options

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

Conditional formatting with custom format

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

Conditional formatting with new rule

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

Conditional formatting dates from last week

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


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

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.
=$D5<NOW() 
  • After that, select the Format feature.

Conditional formatting with NOW function

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.
=$D5>Today()
  • We also formatted the highlighting color from the format section.

Conditional formatting with TODAY function

  • Finally, press the OK button.

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


Similar Readings


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.
  =WEEKDAY(C$8,2)>5  
  • Then, select the desired format by following the steps in Example 1.

Conditional formatting with WEEKDAY function

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, =WEEKDAY(C$8,2)>5; this formula only returns a TRUE value when the days are Saturday (6) and Sunday (7) and formats the cells accordingly.

  • 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: Excel Conditional Formatting Dates


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
  =AND($D8>=$C$4, $D8<=$C$5)
  • Select the desired format by following the steps from Example 1.

Conditional formatting with date in a range

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, =AND($D13>=$C$4, $D13<=$C$6) this formula 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).

  • 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: Excel Formula to Change Cell Color Based on Date


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.
  =MATCH(C$7,$C$14:$C$16,0)

Conditional formatting with MATCH function

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.

=COUNTIF($C$14:$C$16,C$7)>0

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.

Conditional formatting with Less Than option

  • The Less Than window appears.
  • Put the following formula based on the TODAY function in the marked section.
=TODAY()-365 

Conditional formatting for less than one year

  • Finally, press the OK button.


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.
=DATEDIF($D5,TODAY(),''m'')<6 
  • After that, we define the format of highlighted cells as shown in Example 1.

Conditional formatting with DATEDIF function

  • Finally, press the OK button.

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


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.
=TODAY()-$D5>15 

Conditional formatting with past due date

  • At last, press the OK button.

We can change the due day in the formula.


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.
=$C5>$D5

Conditional formatting based on another column

  • Again, press the OK button.

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


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.


Further Readings

Chinmoy Mondol

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo