What Is Conditional Formatting For Dates?
Conditional formatting for dates is an option to format and highlight date values based on the specified conditional formatting rule or criteria.
Users can use the Conditional Formatting feature for dates to highlight overdue dates, expiry dates, specific dates such as weekends, and dates within a specified period.
For example, the table below lists students and their date of birth data.
And the requirement is to check which students had their birthdays on the date specified in cell E1.
Then, we can perform conditional formatting for dates in the past using the Excel Conditional Formatting feature in the Home tab.
In the above conditional formatting for dates in the past example, we choose the cell range B2:B11. And then, we select the New Rule option in the Conditional Formatting feature in the Home tab.
The New Formatting Rule window opens, where we choose the last rule and enter the required conditional formatting rule or formula.
First, the DAY function returns the day and the Excel MONTH function returns the month of the supplied date value.
So, the formula checks each cell date value in the chosen cell range B2:B11, whether the date value’s day and month equal the day and month of the date in cell E1. And as the condition holds in cells B2, B4, B5, and B8, the cells get highlighted in the specified format.
Table of contents
Key Takeaways
- The conditional formatting for dates option enables one to highlight date values in a dataset based on the specified criteria.
- Users can conditionally format dates to point out those overdue, expired, weekends, holidays, or within a specified date range while working on financial statements.
- We can use the Conditional Formatting feature to format dates based on a condition.
- We can use DATE functions, such as DATE and TODAY, with logical operators (‘=’, ‘>’, ’<’, ’>=’, and ‘<=’) to create the appropriate date conditional formatting rule.
How To Use Conditional Formatting For Dates?
The steps to use Conditional Formatting for dates are as follows:
- Choose the cell range containing dates to format based on the given condition, and select the Home tab – Conditional Formatting drop-down option.
- Select the New Rule option from the drop-down list to access the New Formatting Rule window.
Alternatively, we can press Alt + H + L + N to access the New Formatting Rule window.
Otherwise, we can use the keyboard shortcut, Alt + O + D, to access the Conditional Formatting Rules Manager window. And then, select the New Rule option to access the New Formatting Rule window.
- Choose the last rule type to enter the required date formatting condition in the New Formatting Rule window.
- Enter the date formatting condition expression in the Edit the Rule Description section field to highlight the required cells within the chosen range.
- Choose the Format option to access the Excel Format Cells window.
- Click the tabs in the Format Cells window to access various formatting settings we wish to use to highlight the required cells within the chosen range.
- Click OK to exit from the Format Cells window.
- Click OK to exit from the New Formatting Rule window and view the cells highlighted in the chosen cell range based on the specified date formatting condition.
Furthermore, we can repeat the above steps to apply different date conditional formatting rules to the same cell range. But it is best to keep the Format settings unique each time to ensure the output is clear and understandable.
Examples
Check out the following examples explaining how to use the Conditional Formatting feature for dates.
Example #1 – Highlight Due Dates Which Are Due Today
We shall see how to highlight due dates which are due today.
Also, we will see an example of Excel conditional formatting for dates overdue.
The table below lists products, their bill numbers and payment due dates.
And the requirement is to highlight payment due dates which are due today. Then, the steps are as follows:
Step 1: Choose the cell range C2:C8 and select Home – Conditional Formatting – New Rule.
The New Formatting Rule window opens, where we choose the last rule type.
Step 2: Enter the date formatting condition in the field provided in the Edit the Rule Description section.
=$C2=TODAY()
And click Format to access the Format Cells window.
Step 3: Choose the Fill tab to open it and select the required color from the Background Color options.
And then click OK.
And once we click OK in the New Formatting Rule window, the payment due dates due today or the current date get highlighted in the chosen cell range.
The Excel TODAY function in the date formatting condition expression returns the current date. And then, the formula checks if a payment due date in a cell in the chosen column C cell range equals the TODAY() output.
Cells C3:C4 contain the payment due dates which match the current date the TODAY() returns. So, as the date formatting condition holds in cells C3:C4, the cells get highlighted.
On the other hand, if the requirement is to highlight the payment due dates that are overdue and past the due date of 6/15/2023.
Then, we can perform Excel conditional formatting for dates overdue, as shown below:
1: Select the cell range C2:C8, where we previously applied the date formatting condition.
And then, choose Home – Conditional Formatting – Manage Rules.
The Conditional Formatting Rules Manager window opens, where we must click the existing rule and click Edit Rule.
The Edit Formatting Rule window opens to view and edit the existing date formatting condition.
2: Enter the required date formatting condition to highlight cells containing the payment due dates overdue and past 6/15/2023.
=$C2<DATE(2023,6,15)
And choose Format to access the Format Cells window.
3: Click the Fill tab to open it and choose the required color from the Background Color options.
And click OK.
Next, click OK in the Edit Formatting Rule window.
And once we click OK in the Conditional Formatting Rules Manager window, we will see the following output.
First, the Excel DATE function accepts three argument values, year, month, and day. And it returns the date 6/15/2023. And then, the formula checks if a payment due date in a cell in the chosen column C cell range is less than the DATE() output.
Now, Excel considers dates as sequential serial numbers. Thus, an older date will have a serial number smaller than the latest date.
So, if a column C cell contains a date, with its serial number equivalent smaller than the specified date’s serial number equivalent, then the condition holds. And thus, the format set in the New Formatting Rule window applies in the specific cell.
And as the condition holds in cells C2:C4, the cells get highlighted.
Example #2 – Highlight Weekend Dates Using Conditional Formatting
We can use the Conditional Formatting feature to highlight weekend dates in a given cell range.
For example, the table below lists tasks, engineers, and their assignment dates.
And if we must highlight the weekend dates in column C.
Then, we can achieve the desired outcome using the Conditional Formatting feature.
Step 1: Choose cell C2:C21, and select Home – Conditional Formatting – New Rule.
The New Formatting Rule window opens, where we must choose the last rule type.
Step 2: Enter the weekend date formatting condition in the field provided in the Edit the Rule Description section.
=WEEKDAY($C2,2)>5
And then, click Format to access the Format Cells window.
Step 3: Choose the Fill tab and select the required background color.
Next, click OK.
And once we click OK in the New Formatting Rule window, we will obtain the following output.
The Excel WEEKDAY function accepts a date value specified in a column C cell. And the second argument value is 2. So, the function considers a week from Monday to Sunday, implying that numbers 6 and 7 indicate the weekend days, Saturday and Sunday.
And the function counts the day of the specified date in a week. So, if the WEEKDAY() output is greater than 5, the condition holds in the corresponding cell and gets highlighted.
Thus, in this way, we can perform conditional formatting for dates in the future, such as specific days in a forthcoming period.
Example #3 – Highlight Dates Between Two Dates Using Conditional Format
We can conduct conditional formatting for dates within a range of dates specified explicitly.
For example, the first table contains a list of newly joined employees at a firm and their joining dates.
And the requirement is to highlight joining dates in column B between the joining date range specified in the second table.
Then, as the above is a scenario of conditional formatting for dates within a range, we can use the Conditional Formatting feature to obtain the required output.
Step 1: Choose the cell range B2:B20 and select Home – Conditional Formatting – New Rule.
The New Formatting Rule window will open, where we must select the last rule type.
Step 2: Enter the date formatting condition in the field inside the Edit the Rule Description section.
=AND($B2>=$F$2,$B2<=$F$3)
And click Format to access the Format Cells window.
Step 3: Choose the Fill tab to open it and select the required background color.
And click OK.
Click OK in the New Formatting Rule window to view the following output.
The date formatting rule uses the Excel AND function to check if a joining date value in a column B cell is greater than or equals the start date (cell F2) and less than or equals the end date (cell F3). And the column B cells where the condition holds get highlighted.
Example #4 – Highlight All The Holidays Dates Using A Conditional Format
We can use the Conditional Formatting featureto highlight all the holiday dates in a given range.
For example, the first table shows the November calendar, and the second table lists all the holidays in November.
We can highlight the holidays listed in the second table in the first table using the Conditional Formatting feature.
And hence, the above is also a scenario of conditional formatting for dates in the future.
Step 1: Choose the cell range A2:E8 and select Home à Conditional Formatting à New Rule.
The New Rule Formatting window will open, where we must select the last rule type.
Step 2: Enter the date formatting condition in the field inside the Edit the Rule Description section in the New Formatting Rule window.
=COUNTIF($H$2:$H$6,A2)=1
And click Format to open the Format Cells window.
Step 3: Choose the Fill tab and select the required background color.
And click OK.
And once we click OK in the New Formatting Rule window, we will obtain the following output.
The Excel COUNTIF function checks whether a date value from the chosen cell range A2:E8 occurs once in the cell range H2:H6. And if the condition is true in a cell in the chosen cell range A2:E8, the cell gets highlighted.
Important Things To Note
- Excel considers the serial number equivalent of date values while performing the calculations for conditional formatting for dates. So, if a date is older than the given date, its serial number equivalent will be smaller than that of the given date’s serial number equivalent.
- We can apply multiple conditional formatting rules on a given cell range containing date values. But ensure to keep the Format settings distinct each time to avoid confusion.
- The Conditional Formatting feature formats dates without considering the date format in Excel. However, the date format should be valid to avoid errors in Excel.
- Ensure to use the DATE functions to enter date values in the date formatting rules in the Conditional Formatting feature to format dates error-free.
Frequently Asked Questions (FAQs)
We can do conditional formatting in Excel for expiry dates using the Conditional Formatting feature in the Home tab.
For example, the table below lists grocery items and their manufacturing and expiry dates.
And the requirement is to conditional format the expiry dates to highlight those past 6/20/2023. In other words, we aim to highlight the expiry dates of expired items, considering the date 6/20/2023 as the cut-off date.
1: Select the cell range C2:C8 and choose Home – Conditional Formatting – New Rule.
The New Formatting Rule window will open, where we must select the last rule type.
2: Enter the date formatting condition in the field inside the Edit the Rule Description section.
=$C2<DATE(2023,6,20)
And click Format to open the Format Cells window.
3: Select the Fill tab to open it and choose the required background cell color.
And click OK.
Finally, once we click OK in the New Formatting Rule window, it will close, and we will obtain the below output.
The DATE() accepts three arguments, year, month, and day. And it returns the date 6/20/2023. Next, the formula checks each cell date value in the chosen cell range C2:C8, whether the date is older than the specified cut-off date, 6/20/2023.
So, if a date value’s serial number equivalent is smaller than the cut-off date’s serial number equivalent, the condition holds in the corresponding cell. And it gets highlighted in the specified format.
Excel conditional formatting dates within 14 days is possible using the following formula as the conditional formatting rule in the Conditional Formatting feature in the Home tab:
AND(Date Value>(TODAY()-14))
The formula will highlight cells within the chosen cell range that have a date within 14 days of the current date.
Your date conditional formatting is not working, perhaps because of the following reasons:
• The supplied dates are invalid.
• The supplied date values appear as dates, but they are text values.
• The date formatting condition or rule specified in the New Formatting Rule window is incorrect.
Download Template
This article must be helpful to understand the Conditional Formatting For Dates, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Conditional Formatting For Dates. We learn how to highlight dates based on given conditions with examples. You can learn more from the following articles –
Leave a Reply