If you store the birthdays of your customers in your database, you can use this report to give you a list of all those with a birthday next month. You can apply a similar principal to anniversaries, membership start dates etc.

As the year of the stored date could be from any number of years ago, you can't simply add a criteria of 'date is next month'. You instead need to create a calculated column that ignores the year, and only looks at the month of the date.

Step 1: Create a report based on People

You can use a blank report or start the report from a prepopulated report.

Step 2: Add columns

Add columns for the Person Name, Date of Birth, and any other columns, like home address or email address if you are planning on sending these People a birthday message!

Step 3: Add a calculated column

Add a calculated column called 'Month difference'. Use the formula:

MONTH( cf_person_date_of_birth) - MONTH(CURDATE())

This will give you the number of months until their birthday, from 1 to 11, or the number of months it has been since their birthday if they have already had their birthday this year, from -11 to -1. The result will be 0 if their birthday is this month. (See column 'Month diff.1' in screenshot below)

If you want the result to be in the range 1 to 12, i.e. the number of months until it is next their birthday (See column 'Month diff.2' in screenshot below), use the following formula:

(12+ MONTH( cf_person_date_of_birth) - MONTH(CURDATE())) % 12

Step 4: Add criteria

Go to the Criteria tab and add a calculated criterion that says ‘Month difference equals 1’.

You may also want to add another criterion that says ‘Date of Birth is not blank’.

Step 5: Set up a scheduled email

To ensure that the list of People gets sent to the right user each month, you can set up a scheduled report.

Seeing the exact number of days until the birthday

If you want to know the number of days until the Person's birthday, add another calculated column using the following formula:

DAYOFYEAR(cf_person_date_of_birth) - DAYOFYEAR(CURDATE())

If they have already had their birthday this year, this will return a negative number. (See column 'Days diff.1' in screenshot below).  If you would like to see the number of days until their next birthday in the range 1 to 365 (see column 'Days diff.2' in screenshot below) use the following formula:

(365+(DAYOFYEAR(cf_person_date_of_birth) - DAYOFYEAR(CURDATE()))) % 365

(This does not take leap years into account)