How to Use Conditional Formatting for Dates?

The basic formatting concepts everyone can know 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.read more concepts we need to master. One such concept is “Excel conditional formatting for dates.” This article 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.read more.

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. read more 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.read more team. Both receivable and payable teams tend to work with due dates. For an accounts receivable team, collecting the money on time is necessary. 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, let us look at the following invoice data.

From the above, we have “Due Date” in this column. In addition, we have certain due dates over the due date, which are due today and are not yet due so far.

So we will use conditional formatting to highlight all those dates.

Now, let us look at the table in cell C7. It has been highlighted because today’s date is “09th July 2019,” so conditional formatting has highlighted the due date. After all, we use the 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.read more.

  • First, we must select the due date column. Then, we must go to “Conditional Formatting” and click on “New Rule.” Now, choose “Use Formula to determine which cells to format.” In the “Formula” section, we must insert the below formula to identify the due dates, which are “TODAY.” Now, we need to click on “FORMAT” and choose the formatting color as per our choice. Now, click “OK” to apply the formatting.

Similarly, we need to apply to format for due dates beyond the due date. Again, we must use new conditional formatting and insert the below formula.

Now click on “OK.” As a result, it will highlight all the dates beyond the due date.

So, dates 6th July and 30th June are beyond the dates in the table above. However, the TODAY function is a volatile formula. It keeps changing every day, and one needs not to worry about today’s date.

Example #2 – Highlight Weekend Dates using Conditional Formatting

Everyone loves weekends.

So, we can highlight only weekend dates with all the dates available. For example, look at the below data of dates in the entire month.

To highlight all the dates which belong to the weekend, we must follow the below steps.

Note: We assume the weekend comes on “Saturday and Sunday.” So the starting day of the week is “Monday.”

In the formula section of the conditional formatting, we must insert the below formula:

=WEEKDAY(A1,2)>5

We will get the following result.

Here, we have applied the WEEKDAY in excelWEEKDAY In ExcelThe WEEKDAY function in excel returns the day corresponding to a specified date. The date is supplied as an argument to this function. read more formula. The formula reads below.

The WEEKDAY function checks all the selected cell dates and weekday numbers. For example, suppose the week starts from MONDAY (WEEKDAY(A1,2)). So, this will give the weekday number in the week. If the number provided by the WEEKDAY function is >5, i.e., Saturday and Sunday, it will highlight the weekend dates.

Example #3 – Highlight Dates between Two Dates using Conditional Format

For example, let us look at the below data of dates.

We need to identify sales dates from this sales data between 23rd April 2019 and 24th September 2019.

So, we must open the “Conditional Formatting” tab, enter the formula below, and choose the formatting as per choice.

We 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.read more here. It will identify the dates between 23rd April 2019 to 24th September 2019 and use the formatting.

As a result, it will highlight the dates between those two dates.

Example #4 – Highlight All the Holidays Dates using a Conditional Format

For example, we can use conditional formatting if we have a list of dates and want to highlight all the holiday dates.

In the above list, we have dates, and also we have a list of holidays. So, from these dates, we need to highlight holiday dates.

So, in conditional formatting, we must apply the below function.

So, this will highlight all the holiday dates in the date’s list.

Things to Remember

  • Conditional formatting works according to the condition provided.We can format the dates that are today’s due, which are beyond the due date, which are yet to be due etc.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.read more in conditional formatting.

This article is a guide to Conditional Formatting for Dates. We discuss step-by-step examples in a conditional format based on due dates, weekend dates, dates between two dates, holiday dates, examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Conditional Formatting with FormulasConditional Formatting for Blank CellsConditional Formatting in the Pivot TableConditional Formatting Based On Another Cell Value