Forum Discussion
Conditional format list of birthdays to highlight dates
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:
- Select the Range:
- Choose the range of cells that represent your calendar.
- Conditional Formatting Setup:
- Go to the "Home" tab in Excel.
- Click on "Conditional Formatting" in the ribbon.
- New Rule:
- Select "New Rule" from the dropdown menu.
- Use a Formula to Determine Which Cells to Format:
- Choose "Use a formula to determine which cells to format."
- 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.
- Choose the Formatting:
- Click on the "Format" button and select the formatting options you prefer (e.g., fill color like yellow to highlight).
- 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.
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.
