Calculating Birthdays in the last 30 days

**bullodg** · 01-18-2015, 10:09 AM

Hi,

I want excel to alert me if the birthday of my clients was within the last 30 days. I am using the formula

=IF(MONTH(K36)<>MONTH(TODAY())," ",IF(DAY(K36)>DAY(TODAY()-30)," ","Happy Birthday"))

This formula is working to a certain extent but not working when the year changes. For example it works if the date of birth is 10/01/1980 (the month is same as this one January) and but not if the birthday is 30/12/1980 which is also 30 days prior to today (18/01/2015).

The cell contains actual dates of birth e.g. 01/01/1980, 12/10/2000 etc

I also have another problem where some of the people don't have an entry against their DoB field and if it is blank it give a false alert that it is their birthday and if it has a hyphen (-) in the cell it give a #value! alert. How can I solve this?

Could you please help. Thanks in advance

PS: Would this formula work in conditional formatting too.

**daddylonglegs** · 01-18-2015, 10:34 AM

Try using DATEDIF function like this

=IFERROR(IF(K36="","",IF(DATEDIF(K36,TODAY(),"yd")<=30,"Happy Birthday","")),"")

If K36 is blank or "-" (or any other text value) then the formula returns a blank

**bullodg** · 01-18-2015, 11:19 AM

Thanks a lot for the quick response Daddylonglegs, really appreciate it.

This formula works perfectly.

Would I be able to use the same formula in conditional formatting too?

**daddylonglegs** · 01-18-2015, 02:25 PM

If you want to highlight the Birthday dates here it would probably be simpler just to use the "Happy Birthday" text as a "trigger", or if you want that separately just use this formula in CF

=AND(DATEDIF(K36,TODAY(),"yd")<=30,K36<>"")