As the formula is copied down, it will return a “Happy Birthday!” when a birthday in column C matches the current date. Otherwise, the formula will return empty string (""), which displays as a blank cell.
The long way
The traditional “long way” solution to this problem is to use a formula like this: Working from the inside out, the logical test inside the IF function is based on the AND function: Inside the AND function, there are two logical expressions, one that checks the month, and one that checks the day: The MONTH function returns the month for a given date as a number (i.e. MONTH returns 5 for a date in May) . The DAY function returns the day portion of a date. The TODAY function returns the current date. Essentially, the logical expressions inside the AND function check to see if the month and day both match. We purposely ignore the year for birthdays, since the year only matches in the first year of birth. The AND function will only return TRUE if both the month and the day match, which means we have a birthday that lands on the current date. When AND returns TRUE,the IF function will return “Happy Birthday!” as a result. If either the month or day do not match, AND will return FALSE and the IF function will return an empty string (""). This formula works fine, but the TEXT function can streamline the formula somewhat, as explained below.
TEXT function
The TEXT function can also be used to solve this problem in a more elegant way. The TEXT function is used to apply custom number formats inside a formula. By using a number format that contains only the month and day, we can check both values at the same time. In the worksheet shown, the formula in E5 is: Inside the IF function, the logical test is: Here, the TEXT function is used to apply the number format “mmdd” to both the date in column C and the current date, supplied by the TODAY function. In the worksheet shown, the date in C5 is January 15, 1999 and the current date is May 13, 2022. With these dates, the TEXT function returns a text string that includes both the month and day like this: These values don’t match, so the logical test returns FALSE and the IF function returns an empty string (""). However, in row 12 where the date in C12 is May 13, 1999, we have: Here the text values match. The logical test returns TRUE and the IF function returns “Happy Birthday!” as a result.
Message with name
To include the first name in the Happy Birthday message, you can concatenate the name from column B like this : For an overview of concatenation with more formula examples, see: How to concatenate in Excel.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
