Conditional format list of birthdays to highlight dates

CWinfrey 

If your dates in column I are generated by a formula and there are blank spots between dates, it might be due to the nature of your formula. The MATCH function may not work directly with formula-generated dates if there are inconsistencies in the format or if the formula generates errors or blanks.

Instead of using MATCH, you can try using the COUNTIFS function along with the ISNUMBER function to handle the potential blanks. Here's an adjusted formula for conditional formatting:

=AND(ISNUMBER(MATCH(Q$3,$I:$I,0)), Q$3<>"")

This formula checks if the date in the calendar cell matches any non-blank date in the list. The AND function ensures that both conditions (non-blank and matching) are met before applying the formatting.

Remember, the success of this formula depends on the consistency of your date formats and the accuracy of the formula in column I.

If you're still experiencing issues, consider checking the actual values in the cells to see if there are any hidden characters or formatting differences. You can use the CLEAN function to remove non-printable characters, and the TYPE function to check if there are any errors in your date cells.

Here's an example of how to check for errors:

=TYPE(Q$3)

This formula returns 1 for numbers, 2 for text, and 16 for errors. If there are errors in your date cells, you might need to adjust your formula or clean up the data.