Forum Discussion
Conditional format list of birthdays to highlight dates
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.- If I put in birthdays with current year everything works.
I would like to leave actual year as another formula tells me the age.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.