Highlight and get birthdays count from Date of Birth

In this article, you will learn how to get count of persons from a Date of Birth data whose birthdays are in the specific month. You will also learn how to highlight the data using Conditional Formatting whose birthdays are in This Month, Last Month and Next Month.

We have below given Date of Birth data for Employees-

We have created a Data Validation List for This Month, Last Month and Next Month on cell “F3” and then we have used formula on cells “F4” to get the count. Below is the formula –

=SUM(IF(MONTH($C$2:$C$21)=IF(F3="Last Month",MONTH(TODAY())-1,IF(F3="Next Month",MONTH(TODAY())+1,MONTH(TODAY()))),1,0))

After putting this formula just press Shift+Ctrl+Enter to create an array.

To get the count by month name wise birthdays, we have added month name on range “F10:F21” and we have put the below formula on cells “G10”

=SUM(IF(TEXT($C$2:$C$21,"MMM")=F10,1,0))

After putting this formula just press Shift+Ctrl+Enter to create an array and fill-down the formula

To highlight the birthdays dynamically, we have used Conditional Formatting with formula. Below is the formula which we have used in Conditional Formatting–

=OR(AND($F$3="Last Month",MONTH($C2)=MONTH(TODAY())-1),AND($F$3="This Month",MONTH($C2)=MONTH(TODAY())),AND($F$3="Next Month",MONTH($C2)=MONTH(TODAY())+1))