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.
Watch Video – Conditional Formatting Based on Date in Excel
Conditional Formatting Based on Date in Excel: 9 Examples
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.
- 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 Date in Another Cell in Excel
2. Highlight Dates Preceding the Current Date Using NOW or TODAY Function
This example shows how to 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 highlight 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.
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.
- 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.
=WEEKDAY(C$8,2)>5 Â
- Then, select the desired format by following the steps in Example 1.
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: Highlight Row with Conditional Formatting Based on Date in Excel
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.
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: How to Highlight Row Using Conditional Formatting
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)
- 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.
=COUNTIF($C$14:$C$16,C$7)>0
Read More: How to Change Cell Color Based on Date Using Excel Formula
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.
=TODAY()-365Â
- 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.
=DATEDIF($D5,TODAY(),''m'')<6Â
- 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
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Â
- Then, choose the highlighting color from the Format option.
- 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
- 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
Download the following practice workbook to exercise while you are reading this article.
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. If you have any queries you can write us in the comment box below.
