Conditional format list of birthdays to highlight dates

Copper Contributor

I have a log to fill out for PTO and other related events.

I am having trouble getting Conditional Format to highlight date headers and calendar dates with the existing column info for birthdays and Anniversaries.

Example the holidays from my list highlight yellow because they are the current year.

I have tried a few different things but have been getting mixed results.

I would like to use conditional formatting if possible.

I have the dates all as columns and the dates in a column with each in its own row.

I also have an auto generated calendar in another area of the sheet.

I tried to put file on this post but says not supported even though it is a .xlxs file.

I am running Excel in Microsoft 365 apps version 2310.

 

7 Replies

@CWinfrey 

Setting up conditional formatting to highlight specific dates in Excel based on a list of birthdays, anniversaries, or holidays can be done using the Conditional Formatting feature. Here's a step-by-step guide to help you achieve this:

Scenario:

You have a list of dates in one column and want to highlight those dates across a calendar.

Steps:

  1. Select the Range:
    • Choose the range of cells that represent your calendar.
  2. Conditional Formatting Setup:
    • Go to the "Home" tab in Excel.
    • Click on "Conditional Formatting" in the ribbon.
  3. New Rule:
    • Select "New Rule" from the dropdown menu.
  4. Use a Formula to Determine Which Cells to Format:
    • Choose "Use a formula to determine which cells to format."
  5. Set the Formula:
    • Assuming your list of dates starts from cell A2, you can use the following formula to compare the dates in your list to the calendar dates.
    • If your calendar starts from cell B2, the formula would be:

=ISNUMBER(MATCH(B$2,$A:$A,0))

    • This formula checks if the date in the calendar cell matches any date in the list. Adjust the cell references to match your setup.
    • Ensure the dollar signs ($) are correctly placed to make the formula work when applied to other cells in the calendar.
  1. Choose the Formatting:
    • Click on the "Format" button and select the formatting options you prefer (e.g., fill color like yellow to highlight).
  2. Apply the Rule:
    • Click "OK" to apply the conditional formatting rule.

This should highlight the dates in your calendar that match the dates in your list. Adjust the cell references in the formula to match your specific Excel setup.

Remember, the dollar signs in the formula lock certain references, allowing the conditional formatting rule to be applied to the entire calendar. Also, ensure the format you choose to apply is clearly visible and doesn't clash with any existing colors or formatting. The text was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Does that formula work when the year in the date is different?

example today is 11/13/2023 but his birthday or anniversary is 11/13/1988.

 

@NikolinoDE 

@CWinfrey 

The formula provided compares specific dates in your calendar with those in your list, regardless of the year. If your goal is to highlight dates in the calendar that match the month and day of the listed dates, regardless of the year, that formula will indeed work for you.

For instance, if today is 11/13/2023 and the anniversary or birthday in your list is 11/13/1988, the conditional formatting formula will highlight the corresponding cell in the calendar as it matches the day and month, despite the different years.

This formula doesn't consider the year but focuses on the day and month. If you want to highlight dates based on the day and month match, regardless of the year, the provided formula will achieve that for you.

 

Column I has the dates but there are some blank spots between dates.
Q3:JT3 are the dates. (dates are in formula form first day is 1/1/24 and rest are Q3+1 and so on)
I used this formula but get no the color fill I selected. =ISNUMBER(MATCH(Q$3,$I:$I,0))
If you can tell me what I am doing wrong I would appreciate it
Thank You.
If I put in birthdays with current year everything works.
I would like to leave actual year as another formula tells me the age.

@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.

I added a new column that converts the Birthday and Anniversary with the current year.
When I use those dates it works even with the blanks.
Thank You.