How would you display your Contacts with upcoming birthdays? I’ve seen people use “Birthdays This Week,” “Birthdays This and Next Week,” and other reports to display the list.
I’ve also seen requirements for showing a person’s next birthday, to trigger an automatic email to each Contact on his/her birthday. Let’s see how it’s done:
if(
Month (Birthdate) < = Month(Datevalue( NOW() ) ),
if ( Month (Birthdate) < Month(Datevalue( NOW() ) ),
DATE( YEAR( DATEVALUE( NOW() ) ) + 1 , MONTH( Birthdate ) , DAY( Birthdate ) ),
if ( Day (Birthdate) < Day ( Datevalue ( NOW() ) ),
DATE( YEAR( DATEVALUE( NOW() ) ) + 1 , MONTH( Birthdate ) , DAY( Birthdate ) ),
DATE( YEAR( DATEVALUE( NOW() ) ) , MONTH( Birthdate ) , DAY( Birthdate ) )
)
),
DATE( YEAR( DATEVALUE( NOW() ) ) , MONTH( Birthdate ) , DAY( Birthdate ) )
)
Next, use Batch Apex to send an email daily to all people where Next_Birthday__c == date.today(). Easy!
Now we can put this into a report and a dashboard showing the next “n” birthdays. Sure, it’s possible that more than n people will have a birthday on a given day, but at least you know that the emails will go out to each of them! If you want to see everyone with birthdays in a certain range, make a custom report and click through from a dashboard or a custom link.
Enjoy!
RSS Feed
Nice formula David! Saved for future reference and I would think could be used for finding next occurrence of any date just by swapping out the Birthdate field.
Have you tested what happens with Feb 29th?
For my own use, I also reformatted a bit. Commas at the beginning of lines in if statements make it easy to select an entire condition to add or remove for testing. Posting it back here will probably mangle it though so let me know if you want to see how how I reformatted it.
Great formula! Did you look at the Birthday eMailer app on the AppExchnage (by force.com labs)?
http://appexchange.salesforce.com/listingDetail?listingId=a0N30000003I77zEAC
Thanks for sharing that! I took a look, and it’s great for automatically emailing birthdays and anniversaries; it doesn’t, however, provide visibility into upcoming birthdays. A great complement to this formula, though. And a great example of using Batch Apex with email templates.
Thank you for the formula! You made my life easier.
For the click-not-code inclined (like myself) you can also achieve the equivalent to the Apex trigger by adding in a hidden toggle field (I use a number field. Checkbox boolean values cannot be used in workflows). I trigger the initial time-based email workflow action when the toggle value equals 1; I also add a field update to this workflow to set the toggle value to zero.
A separate field update workflow is triggered when the toggle is updated to zero and sets the toggle to back 1 re-triggering the time based email workflow.