Microsoft

Pro tip: Use Excel's conditional formatting to highlight invalid dates

Susan Harkins shows you how to visually identify invalid dates using Excel's conditional formatting feature.

Excel

Last month, we used conditional formatting to highlight holidays in a matrix-style calendar; we added this enhancement to an earlier article that used conditional formatting to highlight weekends. By separating the year, month, and day, you can reuse the same sheet for tracking monthly projects, totals — most any kind of monthly values. This month, we'll make one final enhancement. We'll add one more conditional format rule to black out the 29th, 30th, and 31st on the matrix when those dates aren't valid for the specified month. The conditional formatting rule that we'll add in this article takes care of that, as you can see in Figure A.

Figure A

Figure A

Add a conditional rule that blacks out invalid dates at the end of the month.

The approach you choose should depend on your skill level:

The example file contains the following sheets:

  • 2013 Matrix: The calendar from the original 2013 article that highlights weekends.
  • Holidays: A sheet of holiday dates created in the second article (added last month).
  • Holiday Conditional Format: The matrix that highlights weekends, holidays, and blacks out invalid dates.

Excel 2003 users should note that the .xls file does not support the holiday formatting rule. It relies on the NETWORKDAYS() function, which was added to Excel 2007. When you open the .xls file, you'll notice that the NETWORKDAYS() functions (row 4) error out. The file still contains the Holidays sheet, but the conditional rule doesn't work. Please use a search engine to find a 2003 solution for holidays, as I don't have one to share.

Add the rule

Every month has at least 28 days. Some months have 30 or 31. February has only 28, except in a leap year when it has 29. Our month-end rule uses the DAY() and DATE() functions to determine the number of days in the specified month (C2). If the number of days is less than the numeric day values — 29, 30, and 31 — in AE5:AG5, Excel applies a black fill color to the appropriate cells. Now, let's add that rule:

  1. Select AE5:AG12, the last three days in the matrix.
  2. Click Conditional Formatting in the Styles group (on the Home tab). Choose New Rule from the option's drop-down list.
  3. In the top pane of the resulting dialog, choose Use a formula to determine which cells to format.
  4. Enter the following formula, being mindful of the absolute and relative referencing: =DAY(DATE($C$3,$C$2+1,1)-1)<AE$5
  5. Click Format.
  6. Click the Fill tab, choose black (or the color of your choice), and then click OK. Excel will display the formula and the format (Figure B).
    Figure B
    Figure B
  7. Click OK to apply the new rule.

In Excel 2003, Conditional Formatting is on the Formatting menu. Click Add if necessary, choose Formula Is from the Condition drop-down, and add the formula in step 4. Click Format and use the Patterns tab to find a fill color.

Change the month and year components in C2 and C3, respectively, and watch the rule work. For instance, Figure C shows September 2015 and February 2016. September has 30 days and one holiday; February 2016 is a leap year, which the rule also accommodates.

Figure C

Figure C

The new rule even accommodates leap years.

Knowing that it works probably isn't enough for most of you. You'll want to know how it works (I would). The DATE() function uses the following syntax:

DATE(year, month, day)

to return a valid date. Notice that I said "valid." This function is smart enough to know that February 29, 2015, isn't a valid date even if you feed it those values. As you can see in Figure D, those values return March 1, 2015, not February 29, 2015.

Figure D

Figure D

The DATE() function recognizes invalid dates.

We have to be smarter than the function. By adding 1 to the month component and changing the day value to 1, we force the function to return the first day of the next month as shown in Figure E.

Figure E

Figure E

Return the first day of the next month.

Next, we subtract 1 day from the first day of the next month to return the last day of the specified month. Wrapping all that in a DAY() function returns the total number of days in the month, as shown in Figure F, after changing the cell's format to General. If the resulting value is less than 29, 30, or 31, the rule applies the format.

Figure F

Figure F

The DAY() function returns the number of days in the month.

To illustrate how this rule works, let's evaluate it using September 31, 2015, which doesn't exist:

=DAY(DATE($C$3,$C$2+1,1)-1)<AG$5
=DAY(DATE(2015,9+1,1)-1)<31
=DAY(DATE(2015,10,1)-1)<31
=DAY(DATE(October 1, 2015)-1)<31
=DAY(DATE(September 30, 2015)<31
=DAY(September 30, 2015)<31
=30<31
TRUE

When the rule equals TRUE, Excel applies the format, blacking out that column.

Troubleshooting

If the rule isn't working correctly for you, check its precedence by clicking Conditional Formatting in the Styles group, choosing Manage Rules, and then selecting This Worksheet. If this rule isn't at the top of the list, as shown in Figure G, move it by clicking the Move Up and Move Down arrows, accordingly.

Figure G

Figure G

The rule's position matters.

If that isn't the problem, check the rule's referencing. I mentioned this earlier, but it's easy to make mistakes. The absolute and relative referencing is important. Also check the Applies To range.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox