Excel Tips and Tricks

Highlight and get birthdays count from Date of Birth

Birthday Count

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-

Date of Birth data
Date of Birth data

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.

Birthdays Count
Birthdays Count

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

Month Wise Birthdays
Month Wise Birthdays

 

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))
Conditional Formatting
Conditional Formatting

 

Click here to download the Practice file-

 

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com