Copyright infringement

How to use conditional formatting to highlight due dates in Excel

The repercussions of missing a due date can range from simply adjusting the date to getting fired. Don’t risk deadlines when a simple conditional formatting reminds you.

Missed deadlines

Photo: iStock/z_wei

Most of us keep track of project management dates, appointments, etc. No matter how simple or complex the paper is, the due date isn’t worth much if it slips unnoticed. You can set a reminder in Outlook, but there is no such feature in Microsoft Excel – that is not its purpose. However, it is easy to catch your eye with a format that alerts you to the due date. In this article, we’ll use conditional formatting to mark due dates.

We see: 69 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use older versions. You can work with your own data or download the .xlsx demo file. However, you should change the date examples to the current date (this will make more sense later). The browser version will support most conditional formats, but you cannot apply a formula rule.

How to determine the due date in Excel when it is today

Was that due today? It all happened. Missing a deadline is easy enough even though we do our best to stay on track. The simplest alert is to highlight all projects due today. At least that way, we can contact others to alert them that we’re changing the due date – or make any needed adjustments.

We see: How to use Find All to process specific match values ​​in Excel (TechRepublic)

The paper described in Figure A Simple on purpose so we can focus on conditional formatting that highlights any date that matches the current date. Cell C1 returns the results of the =TODAY() function. You may not need this, but I added it as a visual guide. Now, let’s set a conditional formatting rule that highlights due dates that match the current date, which in this case is February. 13, 2021:

  1. Select the data. In this case, it’s C4: C8.
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. From the dropdown menu, choose New Rule.
  4. In the resulting dialog, choose Format Cells That Contain Only at the Top.
  5. At the bottom, choose the dates that occur from the first drop-down list.
  6. In the second dropdown menu, choose Today (Figure A).
  7. Click Format, then click the Font tab.
  8. From the Color dropdown menu, choose Red, and click OK twice.

Figure A

excelduedatecf-a.jpg

Choose an option today.

Figure B

excelduedatecf-b.jpg

One task due today!

as you see in Figure B, the format changed the font color for the record in row 5 to red. Right away, you know the project is due today. Now, you may want to highlight the project as well. In this case, you will need a formula base. But first, choose Manage Rules from the Conditional Formatting dropdown menu and delete the first rule. To add this new rule, do the following:

  1. Select B4: D8.
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. From the dropdown menu, choose New Rule.
  4. In the resulting dialog, select the last option at the top: Use a formula to select the cells to format.
  5. At the bottom, enter the following function (Figure C):
    = C4 dollars = Today ()
  6. Click Format, and then click the Font tab.
  7. From the Color drop-down list, choose Red, and click OK twice.

Figure C

excelduedatecf-c.jpg

This time you need a formula.

This time, the rule highlights the entire row, as shown in figure d. However, at this point, you might be wondering if this log should be marked at all because the task has already been completed. Maybe you are right. Most people do not want to be distracted by this record. If this is the case, you need to add a second condition to the rule: if the date equals today And Column D does not equal Yes, highlight the record.

figure d

excelduedatecf-d.jpg

The formula rule highlights the entire record.

in a Figure E, you can see that I changed the date in grade 6 to February. 13, so you can see this rule in action. The record in row 5 is not marked because it is complete. The record in row 6 is highlighted because the due date is today but is not completed. To implement this rule, delete the second rule and add this rule:

=AND ($C4 = TODAY(), $D4 “yes”)

Figure E

excelduedatecf-e.jpg

Two records have the same date, but one is distinct and the other is not.

keep following

It’s easy to mark a record based on the current date, but when dealing with tasks that you must complete, you may prefer a conditional format that alerts you well before the due date. In an upcoming article, we will take on this task.

see also