Home>Blogs>Excel Tips and Tricks>Highlight and get birthdays count from Date of Birth
Birthday Count
Excel Tips and Tricks

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-

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


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:

Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!