How to Use Conditional Formatting for Dates?
Basic formatting concept everybody knows but based on certain condition formatting is the concept we need to master. One such concept is “Excel Conditional Formatting for Dates.” In this article, we will show you how to format dates under conditional formatting.
The following are examples of conditional formatting based on the date range in excel.
Example 1 – Highlight Due Dates Which are Due Today
When you work with the accounts team, either you work in the accounts receivable team or in the accounts payable team. Both receivable and payable teams tend to work with due dates; for a receivable team, it is necessary to collect the money on time, and similarly, for a payable team, it is important to payout all the payments on or before the due date to avoid consistent follow-ups from vendors.
For example, look at the following invoice data.
From the above, we have “Due Date” in this column; we have certain due dates which are over the due date, which are due today, and which are not yet due so far.
So we will use conditional formatting to highlight all those dates.
- Step 1: Select the due date column.
- Step 2: Go to Conditional Formatting and click on “New Rule.”
- Step 3: Now choose “Use Formula to determine which cells to format.”
- Step 4: In the formula section, enter the below formula to identify the due dates, which are TODAY.
- Step 5: Now click on FORMAT and choose the formatting color as per your wish.
- Step 6: Now, click ok to apply the formatting.
Now look at the table in cell C7; it has highlighted because today’s date is “09th July 2019,” so conditional formatting has highlighted the due date, which is due today in excel.
Similarly, we need to apply to format for due dates, which are beyond the due date. For this, apply new conditional formatting and enter the below formula.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Now click on ok, it will highlight all the dates which are beyond the due date.
So, dates 6th July & 30th June are beyond the due dates in the above table. Since TODAY is the volatile formula, it keeps changing every day and needs not to worry about today’s date.
Example #2 – Highlight Weekend Dates using Conditional Formatting
Everybody loves weekends, isn’t it??
Yes, I am one of them!!!
So, with all the dates available, we can highlight only weekend dates as well. For example, look at the below data of dates in the entire month.
To highlight all the dates which belong to the weekend, follow the below steps.
In the formula section of the conditional formatting, enter the below formula.
We get the following result.
Here I have applied WEEKDAY in excel formula. The formula reads below.
WEEKDAY function checks all the selected cell dates weekday number if the week starts from MONDAY (WEEKDAY(A1,2)). So this will give the weekday number in the week if the number given by the WEEKDAY function is >5, i.e., Saturday and Sunday, then it will highlight the weekend dates.
Example #3 – Highlight Dates between Two Dates using Conditional Format
For example, look at the below data of dates.
From this sales data, we need to identify sales dates, which are between 23rd April 2019 to 24th September 2019.
So, as usual, open the conditional formatting tab and enter the below formula and choose the formatting as per your wish.
I have applied the AND function in excel here. This will identify the dates between 23rd April 2019 to 24th September 2019 and apply the formatting.
This will highlight the dates between those two dates.
Example #4 – Highlight All the Holidays Dates using Conditional Format
For example, if you have a list of dates and you want to highlight all the holiday dates, we can use conditional formatting.
In the above list, we have dates, and also we have a list of holidays. From these dates, we need to highlight holiday dates.
So, in the conditional formatting, apply the below function.
So this will highlight all the holiday dates in the date’s list now.
Things to Remember
- Conditional formatting works based on the condition provided.
- We can format the dates which are due today, which are beyond the due date, which are yet to due etc.
- By using formulas in the condition, we can conduct logical tests.
- We can apply only logical tests in excel in conditional formatting.
This has been a guide to Conditional Formatting for Dates. Here we discuss step by step examples to conditional format based on Due Dates, Weekend Dates, Dates between Two Dates, and All Holidays Dates along with examples and a downloadable excel template. You may learn more about excel from the following articles –