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 excel conditional formatting based on the date range.
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 on 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.
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 an 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 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 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 conditional 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 downloadable excel template. You may learn more about excel from the following articles –