Lagos, Nigeria
info@ezefidelity.com

How to use conditional formatting to highlight due dates in Excel


The ramifications of missing a due date can range from simply adjusting the date to getting fired. Don’t take chances with deadlines when a simple conditional format can remind you.

missing deadlines

Image: iStock/z_wei

Most of us track dates for project management, appointments, and so on. Regardless of how simple or complex the sheet, a due date isn’t worth much if it slips by unnoticed. You can set a reminder in Outlook, but there’s no such feature in Microsoft Excel—that isn’t its purpose. However, it’s easy to grab your attention with a format that alerts you to the due date. In this article, we’ll use conditional formatting to highlight due dates.

SEE: 69 Excel tips every user should master (TechRepublic)

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

How to highlight the due date in Excel when it’s today

“That was due today?” We’ve all had it happen. Missing a deadline is easy enough to do even though we do our best to stay on track. The simplest alert is to highlight all projects that are due today. At least that way, we can contact others to alert them that we’re changing the due date—or make whatever amends are needed.

SEE: How to use Find All to manipulate specific matching values in Excel (TechRepublic)

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

  1. Select the data. In this case, that’s C4:C8.
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. From the dropdown, choose New Rule.
  4. In the resulting dialog box, choose Format Only Cells That Contain in the upper pane.
  5. In the lower pane, choose Dates Occurring from the first dropdown.
  6. In the second dropdown, choose Today (Figure A).
  7. Click Format, then click the Font tab.
  8. From the Color dropdown, choose Red, and click OK twice.

Figure A

excelduedatecf-a.jpg

  Choose the Today option.

Figure B

excelduedatecf-b.jpg

  One task is due today!

As you can see in Figure B, the format changed the font color for the record in row 5 to red. Instantly, you know a project is due today. Now, you might want to highlight the project as well. In that case, you’ll need a formulaic rule. But first, choose Manage Rules from the Conditional Formatting dropdown 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, choose New Rule.
  4. In the resulting dialog box, choose the last option in the upper pane: Use a formula to determine which cells to format.
  5. In the lower pane, enter the following function (Figure C):
    =$C4=TODAY()
  6. Click Format and then click the Font tab.
  7. From the Color dropdown, 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. At this point, though, you might be wondering if this record should be highlighted at all because the task is already complete. You’re probably right; most people would 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 doesn’t equal Yes, highlight the record.

Figure D

excelduedatecf-d.jpg

  The formulaic rule highlights the entire record.

In Figure E, you can see that I changed the date in row 6 to Feb. 13, so you can see this rule at work. The record in row 5 isn’t highlighted because it’s complete. The record in row 6 is highlighted because the due date is today but it isn’t complete. To implement this rule, delete the second rule and add this one:

=AND($C4=TODAY(),$D4<>”yes”)

Figure E

excelduedatecf-e.jpg

  Two records with the same date, but one is highlighted and the other isn’t.

Stay tuned

It’s easy to highlight a record based on the current date, but when dealing with tasks that you must complete, you might prefer a conditional format that alerts you well before the due date. In a future article, we’ll take on that task. 

Also see



This post was written by and was first posted to TechRepublic



Do you find this article helpful? Your Friend might too. So, please Share it with them using the Share button above.
Will you like to get notified when I post new updates? Then Follow me on any of my social media handles: Google News, Telegram, WhatsApp, Twitter, Facebook, Pinterest.
You can also drop your email address below if you wish to be notified by mail.


Tags: ,

%d bloggers like this: