How to Use Conditional Formatting for Dates?
Basic formatting concept everybody knows but based on certain condition formattingCondition FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab. 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 excelDate Range In ExcelTo create a data range in Excel, click anywhere in the table and then go to table tools>design on the ribbon>convert to range. To do so, right-click the table and select table>convert to range..
Example 1 – Highlight Due Dates Which are Due Today
When you work with the accounts team, either you work in the accounts receivableAccounts ReceivableAccounts receivables is the money owed to a business by clients for which the business has given services or delivered a product but has not yet collected payment. They are categorized as current assets on the balance sheet as the payments expected within a year. team or in the accounts payableAccounts PayableAccounts payable is the amount due by a business to its suppliers or vendors for the purchase of products or services. It is categorized as current liabilities on the balance sheet and must be satisfied within an accounting period. 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.
Steps to use conditional formatting to highlight dates are as follows.
- Select the due date column.
- Go to Conditional Formatting and click on “New Rule”.
- Now choose “Use Formula to determine which cells to format”.
- In the formula section, enter the below formula to identify the due dates, which are TODAY.
- Now click on FORMAT and choose the formatting color as per your wish.
- 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 excelToday In ExcelToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the time..
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 excelWEEKDAY In ExcelWEEKDAY Function calculates the given weekday for a specified date. It takes a date as an argument and the return type then returns an integer result ranging from 1-7 as there are seven days in a week. 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 excelAND Function In ExcelThe AND function in Excel is classified as a logical function; it returns TRUE if the specified conditions are met, otherwise it returns FALSE. 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 excelLogical Tests In ExcelA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test. 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 –