How to use TODAY() to highlight fast approaching dates in an Excel sheet

Many apps track dates for many different reasons. You can track due dates, delivery dates, appointments, and more. Depending on how you use these dates, you may want to highlight specific dates as they approach the current date in Microsoft Excel. Likewise, you might want to highlight future dates. By using the TODAY() function and a few conditional highlighting rules, you’ll never be caught out.

SEE: 83 Excel tricks every user should master (TechRepublic)

I’m using Microsoft 365 on a 64-bit Windows 10 system, but you can use older versions. For your convenience, you can download the .xlsx and .xls demo files. Excel Online will show conditional formats, but you can’t create them in the browser.

Highlight today

While you probably don’t want to wait for a due date to start a project, highlighting the current date can help alert you when timing is critical. Fortunately, Excel’s TODAY() function always equals the current date, so you don’t need to update the rule or even include an input value. Let’s add a conditional format that always highlights the current date:

1. Select the cells or rows you want to highlight. In this case, select B3:E12—the data range.
2. Click the Home tab, then click Conditional Formatting in the Styles group and choose Cell Highlighting Rules.
3. Choose An occurrence date.
4. In the resulting dialog box, choose Today from the first drop-down list, then choose Light Red Fill from the second (Figure A). As you can see, the current date is February 15. (Because you will be working with this at a later date, the current date will be different from mine.)
5. Click OK.

Figure A

This is the easiest way to quickly apply a conditional format to highlight the current date, but it’s a bit limited. First, you only have a few format combinations available. Second, it doesn’t highlight the whole row, only the cell containing the date. This rule is able to detect the dates in the selected range and apply the format only to those cells. So it doesn’t matter where the date column is relative to the selected range.

Highlight yesterday and tomorrow in Microsoft Excel

There are built-in rules for yesterday and tomorrow, but let’s go into a rule instead, so you know what to do when there isn’t a proper built-in rule. Let’s start with yesterday:

1. Select data range B3:E12.
2. Click the Home tab, then click Conditional Formatting in the Styles group and choose New Rule.
3. In the resulting dialog box, select the Use a formula to determine the cells to format option in the upper pane.
4. In the lower pane, type the expression =$B3=TODAY-1.
5. Click Format.
6. Click on the Fill tab, choose red and click OK. Figure B shows the rule and format.
7. Click OK to apply the format.

Figure B

As you can see in Figure B, the rule highlights the entire record when the date in column B is yesterday. Note that the rule has an absolute column reference ($B). If you omit the dollar sign, Excel applies the highlight to the cell instead of the entire row. The reference to row 3 is not absolute, so the rule can evaluate all rows in the selected range.

The simple expression TODAY()-1 subtracts one from the current date. It’s the same as yesterday.

To highlight tomorrow, repeat the steps above, but enter the rule =$B3=TODAY()+1as shown on the Figure C. Click on Format and choose any color, but I chose medium blue. Click OK (twice) to apply the format, which highlights the entire row when the date in column B is tomorrow. Again, absolute and relative referencing is important.

Figure C

The three rules are simple to implement, but they are limited to today, yesterday and tomorrow. What if you want to highlight other daily increments beyond these three?

Highlight beyond in Excel

There may come a time when you want a little more flexibility to highlight important dates. For example, you might want to highlight dates that are a week ahead or a week past. When this is the case, I recommend using an input cell where you can specify the days. By referencing the input cell in the formula, the highlight will automatically update according to your needs at the time.

Using Figure D for guidance, format two input cells: 1.) days in the past and 2.) days in the future. Accordingly, we will reference C1 in the past rule and C2 in the future.

Figure D

Let’s first get into the rule for the past tense:

1. Select data range B5:E14. (Note that I updated the range rows because I inserted rows for the input cells.)
2. Click the Home tab, then click Conditional Formatting in the Styles group and choose New Rule.
3. In the resulting dialog box, select the Use a formula to determine the cells to format option in the upper pane.
4. In the lower pane, enter the expression =$B5=TODAY-$C$1.
5. Click Format.
6. Click on the Fill tab, choose yellow and click OK. Figure E shows the rule and format.
7. Click OK to apply the format.

Figure E

At first the rule seems not to work because nothing happens because C1 is empty. Figure E shows the result of entering 3 in C1 – you need an actual input value to see the rule applied. Again, the absolute address, $C$1 is important. If you leave this parent, the highlight will not work as expected.

Repeat the above steps to apply a highlight for the future. Except in step 4, enter the expression =$B5=TODAY()+$C$2and choose green. Figure F shows the resulting sheet after entering the value 3 in C2.

Figure F

With the input cells and rules in place, feel free to change the values ​​in C1 and C2. If the value is too far in the past or in the future, Excel will not apply the highlight. If C1 or C2 is empty, Excel will not apply this rule. Go ahead now and modify these dates to see how the two rules work.

At the moment, the sheet is a little busy. Chances are you don’t want to apply all of these rules at the same time as the demo file does.

The only thing to consider is the position of the rulers. For example, if you enter 1 and 1 in C1 and C2, the last rules you entered will take precedence because they are Excel’s first evaluations. The rules representing the input cells were entered last, so they take precedence over the two rules you entered for yesterday and today earlier.

To change this, open the rules using Manage Rules (in the Conditional Formatting drop-down list) and move the rules accordingly. Or use a data validation rule for C1 and C2 that rejects the value 1 if you don’t want to change the rule positions.

Another thing you might consider. Change the fill color of C1 and C2 to match the highlight fill color in the ruler as shown in G-figure. This will offer a visual clue to users so that they don’t have to remember what those two colors mean.

G-figure