Happy birthday message

In this example, the goal is to display a Happy Birthday message when a birthday in a given cell matches the current date. The core of the problem is to compare the given birthday to the current date while ignoring the year. There are two main ways to approach the problem. The first way is to use the MONTH function and the DAY function to compare the birthday in column C to the current date. This works fine, but the formula is a bit more complex. The second way is to use the TEXT function with a custom number format to compare the birthday in column C to the current date. This solution is a bit more elegant. Both methods are described below.

The long way

The traditional "long way" solution to this problem is to use a formula like this:

=IF(AND(MONTH(C5)=MONTH(TODAY()),DAY(C5)=DAY(TODAY())),"Happy Birthday!","")

Working from the inside out, the logical test inside the IF function is based on the AND function:

AND(MONTH(C5)=MONTH(TODAY()),DAY(C5)=DAY(TODAY()))

Inside the AND function, there are two logical expressions, one that checks the month, and one that checks the day:

MONTH(C5)=MONTH(TODAY()) // check month
DAY(C5)=DAY(TODAY()) // check 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:

=IF(TEXT(C5,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy Birthday!","")

Inside the IF function, the logical test is:

TEXT(C5,"mmdd")=TEXT(TODAY(),"mmdd")

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:

=TEXT(TODAY(),"mmdd") // returns "0513"
=TEXT(C6,"mmdd") // returns "0115"

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:

=TEXT(TODAY(),"mmdd") // returns "0513"
=TEXT(C12,"mmdd") // returns "0513"

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 :

=IF(TEXT(C5,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy Birthday, "&B5&"!","")

For an overview of concatenation with more formula examples, see: How to concatenate in Excel.