With Conditional Formatting in Excel, you can use dates to create a rule for highlighting cells. There are some pre-built rules that you can use to highlight the cells quickly. And there is also a way to create a custom rule based on a date. In this tutorial, we learn to use both ways in detail.
Steps to use Dates for Conditional Formatting
- Select the data where you have dates.
- Go to Home > Conditional Formatting > Highlight Cells Rules.
- From there, click on the option “A date occurring”.
- In the dialog box, select the rule you want to use.
- In the end, click OK to apply.
And the moment you click OK, it highlights all the cells where you have the date occurring in the current month. With the same dialog box, you have more options to use with the dates.
Create a Custom Rule Based on Dates
There could be situations when you need to apply a conditional formatting rule, but that rule is not there. Well, you can create a custom rule with a formula. Go to Home > Conditional Formatting > New Rule.
Highlight Dates Older than 30 Days, 60 Days, and 90 Days
- First, select the data, open the New Rule dialog box, and click “Use a formula to determine which cell to format”.
- 30 Days Older: =$A1<=(today()-30)
- 60 Days Older: =$A1<=(today()-60)
- 90 Days Older: =$A1<=(today()-90) =(today()-90)
=(today()-60)=(today()-30) - After that, to specify the formatting click on the “Format” button.
- Next, specify the formatting, cell color, font color, or bold or italic.
- In the end, click OK.
You can see in the above example that we have used formula to highlight dates older than 30 days from the current date.
Highlight Weekdays and Weekend Dates
And using a formula (Weekday), you can highlight dates that are weekends. You can see in the example below the formula we have used highlights two dates.
=WEEKDAY($A1,2)>5
And if you want to highlight weekdays, use the formula below.
=WEEKDAY($A1,2)<5
Range of Dates
In the same way, if you want to highlight cells based on a date range, you can use the function AND to create a formula with two conditions.
=AND(A1>=(TODAY()-45),A1<=(TODAY()-30))
In the above example, we have created a range of 15 days. 30 days from the current date and 45 days from the current date.
Using a Date from Another Cell
Now, if you want to use the date in a cell, you need to refer to a cell where you have that date. See the example below; we have specified cell C1, where we have the date.
=IF($C$1<>"",$A1>=$C$1,FALSE)
In this way, you can create a dynamic rule, and changing the date in the cell will also change the highlighted cells.
Get the Excel File
DownloadMore on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Remove Conditional Formatting
- Apply Conditional Formatting on Blank Cells in Excel
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel
- Why Conditional Formatting Not Working in Excel
- Highlight IF a Cell Contains a Specific Text
- Applying Color Scales using Conditional Formatting
- Data Bars in Excel using Conditional Formatting
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel