Conditional Formatting For Dates

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Sheeba M

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is Conditional Formatting For Date?

The Conditional Formatting for Dates is a feature where we highlight different dates in a dataset, such as due dates, weekends, holidays, date range, etc. The best way to do so is by using the “Conditional Formatting” by creating a New Rule, or by entering a simple formula. We can also use the “Format Painter” option available in Excel.

For example, in a month, we can highlight the weekends as shown below.

Table of contents
  • The Conditional Formatting for Dates helps in highlighting the required dates while preparing a calendar w.r.t holidays, weekdays, weekends, important dates, etc.
  • Since the formatting is for the dates, we use the TODAY and WEEKDAY functions, and also, for a date range, we use the COUNTIF function. If we must compare the dates as greater than, less than, or equal to, we can use the AND function.
  • The best application for this feature is when we want to highlight dates in a calendar for the upcoming project plan, and the dates just pop up with the formatted rules.

How To Use Conditional Formatting For Dates?

We can use the Conditional Formatting for Dates by setting a rule to highlight the required dates using the “Conditional Formatting” option in the “Home” tab.

Using Excel Conditional Formatting for Dates, we can perform the following for a date range in excel, namely,

  • Highlight Due Dates Which are Due Today.
  • Highlight Weekend Dates using Conditional Formatting.
  • Highlight Dates between Two Dates using Conditional Format.
  • Highlight All the Holidays Dates using a Conditional Format.

Examples

We will consider examples to perform the above-mentioned highlighting.

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 the accounts payable team. Both 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.

Conditional Formatting for Dates Example 1

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.

The steps to use Conditional Formatting to highlight dates are as follows:

  1. First, select the due date column.


    Conditional Formatting for Dates Example 1.1.0

  2. Then, go to Conditional Formatting, and click New Rule.


    Conditional Formatting for Dates Example 1.2

  3. Now, choose “Use Formula to determine which cells to format”.


    Conditional Formatting for Dates Example 1.3

  4. In the Formula section, insert the TODAY formula, shown below, to identify the due dates.


    Conditional Formatting for Dates Example 1.4

  5. Now, click FORMAT, and choose the formatting color as desired.


    Conditional Formatting for Dates Example 1.5

  6. Now, click OK to apply the formatting.


    Conditional Formatting for Dates Example 1.6

Now, let us look at the table in cell C7. It is highlighted because today’s date is “09th July 2019”, so Conditional Formatting has highlighted the due date. After all, we use the today in excel.

Similarly, we must apply to format for due dates beyond the due date. Again, we must use new Conditional Formatting, and insert the formula, as shown below.

Example 1.7

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

Conditional Formatting for Dates Example 1.8

So, the 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 daily, and no need to worry about today’s date.

Example #2 – Highlight Weekend Dates using Conditional Formatting

We will highlight only weekend dates with all the dates available.

For example, look at the below data of dates in the entire month.

Example 1.9

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

In the formula section of the Conditional Formatting, insert the formula:

=WEEKDAY(A1,2)>5

Conditional Formatting for Dates Example 1.10.0

We will get the following result.

Example 1.11

Here, we have applied the WEEKDAY in excel formula with the following syntax,

Conditional Formatting for Dates Example 1.12

The WEEKDAY function checks all the selected cell dates and weekday numbers. For example, suppose the week starts from MONDAY (WEEKDAY(A1,2)), it 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.

Example 1.14

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

Conditional Formatting for Dates Example 1.15

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

Example 1.16

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.

Conditional Formatting for Dates Example 1.17

In the above list, we have dates, and also a list of holidays that we need to highlight.

So, in Conditional Formatting, we must apply the below function.

Example 1.18

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

Conditional Formatting for Dates Example 1.19

Important Things To Note

  • Conditional Formatting works according to the new rule provided.
  • We can format the dates that are due today, 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 excel in Conditional Formatting.

Frequently Asked Questions

1. Define Conditional Formatting for Dates in Excel.

Conditional Formatting for Dates is a feature mainly used for highlighting the dates in a selected dataset or a cell range, based on set criterias. It provides a clear distinction between the highlighted dates and the other dates that are easy to comprehend and keep track in a large dataset.

2. Once applied, how can we remove the Conditional Formatting in Excel?

We can remove the Conditional Formatting for Dates as follows:
• First, choose the entire dataset or the cell range where the Conditional Formatting Rules are created.
• Next, select the “Home” tab - go to the “Styles” group - click the “Conditional Formatting” option drop-down - click the “Clear rules” option right-arrow - select the “Clear Rules from Selected Cells” option, as shown below.

Conditional Formatting - Clear Rules

Then, all the date cells with Conditional Formatting get cleared as it was at the start.

3. Is there an alternative way to copy the Conditional Formatting in Excel?

An alternate way to copy the Conditional Formatting Rule is by using the “Format Painter”.
• First, select the cells with the Conditional Formatting Rule you want to copy.
• Next, follow the path Home> Clipboard>Format Painter, as shown below.

Format Painter

• Finally, apply it to the cell value to paste the Conditional Formatting. Then, the date format, the highlight color, and everything will get automatically formatted.

Download Template

This article must help understand Conditional Formatting for Dates with its formulas and examples. You can download the template here to use it instantly.