Highlight Current Month Birthdays in Excel

imageAugust seems to be a very popular birthday month among my Excel friends. I won’t mention any names here, because most of them are quite elderly, and the shock might upset them. ;-) Anyway, to all of them, and you, if you’re celebrating this month — happy birthday!

The Birthday List

If you’re in charge of an employee list, and have to send birthday greetings, or hiring date anniversary congratulations, you can use Excel to help you keep track.

In the worksheet shown below, there are fake employee names and birth dates. In a separate column you could use the MONTH function to check which birth dates are in the current month.

This formula compares the month of the date in column C, to the month of today’s date. If the months are the same, the result is TRUE.

=MONTH(C2)=MONTH(TODAY())

condformatbirthmonth00

Highlight Birthdays with Conditional Formatting

Instead of adding another column with formulas, you could use conditional formatting to highlight the current month’s dates in column C.

To highlight the dates:

  • Select all the dates in column C. In this example, cell C2 is the active cell.
  • On the Ribbon’s Home tab, click Conditional Formatting
  • Click New Rule
  • In the New Formatting Rule dialog box, click on Use a Formula to Determine Which Cells to Format
  • In the Format box, type the formula to compare months:
    =MONTH(C2)=MONTH(TODAY())
  • Click the Format button, and select the formatting you want for the highlighted cell. In this example, the cells will be filled with light blue.

condformatbirthmonth01

  • Click OK to apply the conditional formatting.

Spot the Birthdays

On the worksheet, all the birthdays from the current month are highlighted with the formatting that you selected. When you open the file at the start of each month, you’ll quickly spot all the fake employees who are celebrating their birthdays.

condformatbirthmonth03

__________________

You may also like...

7 Responses

  1. JP says:

    I hope those are fake names and DOBs! ;-)

  2. It’s okay, JP, at least I took out the Social Security Numbers!
    Actually, I created all the fake names with my Excel random name shuffler, and the birthdays are the result of a RANDBETWEEN function.

  3. aGNES says:

    I LIKE THE CONDITIONAL FORMATTING you’ve just posted, IT IS MORE EASY FOR ME TO DETERMINE who are celebrating the bday per month, I will be using this!.thanks..usually, i’d rather go for the BDAY month columns on my data then filter -hassle!!

  4. Hi there Debra

    If you use =YEAR(C2)=YEAR(TODAY()) instead of =MONTH(C2)=MONTH(TODAY(), would this let me find all Excel s/s birthdays in, say, 2014 – i.e. all those that would be 40 in that year?

    Many thanks in advance

    Robin

  5. Jean says:

    I am wondering if it is possible to have the entire row or column highlight instead of just one specific cell. For example, I am tracking rent amounts for each month and I could like the entire column of October to highlight not just the date.

    Thanks,
    jean

  6. Racquel says:

    Would it be possible to create a macro that organized all true dates in the specific date ranges so you wouldn’t have to scroll through to find every highlighted row, it would automatically pull these results in a separate column.

  7. Chandra says:

    This formula is working fine for me, how to ignore the blank cell from this formula

Leave a Reply

Your email address will not be published. Required fields are marked *