Conditional Formatting for Dates

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.

You can download this Conditional Formatting for Dates Excel Template here – Conditional Formatting for Dates Excel Template

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.

Conditional Formatting for Dates Example 1

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.

Example 1.1.0

  • Step 2: Go to Conditional Formatting and click on “New Rule.”

Conditional Formatting for Dates Example 1.2

  • Step 3: Now choose “Use Formula to determine which cells to format.”

Example 1.3

  • Step 4: In the formula section, enter the below formula to identify the due dates, which are TODAY.

Conditional Formatting for Dates Example 1.4

  • Step 5: Now click on FORMAT and choose the formatting color as per your wish.

Example 1.5

  • Step 6: Now, click ok to apply the formatting.

Conditional Formatting for Dates Example 1.6

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.

Example 1.7

Now click on ok, it will highlight all the dates which are beyond the due date.

Conditional Formatting for Dates Example 1.8

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.

Example 1.9

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

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

In the formula section of the conditional formatting, enter the below formula.

Conditional Formatting for Dates Example 1.10.0

We get the following result.

Example 1.11

Here I have applied WEEKDAY in excel formula. The formula reads below.

Conditional Formatting for Dates Example 1.12

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.

Example 1.14

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.

Conditional Formatting for Dates Example 1.15

This will highlight the dates between those two dates.

Example 1.16

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.

Conditional Formatting for Dates Example 1.17

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.

Example 1.18

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

Conditional Formatting for Dates Example 1.19

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.

Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>