Formula help for birthdate

Question

I need to create a formula that will trigger a workflow and send an email when a Salesforce Contact is going to turn 71 this year. I'm thinking I should know the answer to this question but I can't seem to get it right.

Right now I have the following which I think will work.

Year(TODAY()) - Year(Birthdate)= 71

I'm hoping this will fire at the beginning of the year for the rest of that year. I'm not sure that is what it will do. I don't need to take into account if the Bday has already passed, just if it will be this year and the Contact will be 71. Thanks, Heidi

Answer 1

As is, your current workflow criteria formula would evaluate to true for the entire calendar year (Jan 1 - Dec 31) during which your contact is turning 71 years old.

So far, so good. This sounds like exactly what you're expecting to happen.

With workflow rules, however, you need to keep in mind that they aren't evaluated on a schedule. Workflow rules can be evaluated when a record is created, every time a record is updated, or when a record is updated to meet some criteria (and previously did not meet that criteria).

If you don't edit the contact record, the workflow won't be evaluated.

Using that workflow evaluation criteria, and having the workflow send an email alert, can result in your email being sent multiple times in a single day, for the entire year.

To me, the thought of getting the same email for an entire year is...not appealing, to put it mildly (even less so if it can happen multiple times per day). A more precise explanation of the context surrounding this email might help, but I'd personally be annoyed after about day 2 or 3 of this.

I'd strongly advise putting a rate-limiting mechanism in place. Creating a new Date field on the Contact object, let's call it turning_71_workflow_email_last_sent__c, is almost all you'd need.

You can add and subtract with Date and DateTime fields in formulas, so to limit your workflow rule to sending out an email only every 3 days could be

AND(
    TODAY() - turning_71_workflow_email_last_sent__c >= 3,
    Year(TODAY()) - Year(Birthdate)= 71
)

You would likely want to include a workflow field update in the workflow actions, along with your email alert, to update turning_71_workflow_email_last_sent__c

Conclusion

Your proposed workflow evaluation formula will do what you expect it to, but I'm not sure if workflow is the tool that you should use in this case.

There are probably other factors that you should be taking into consideration as well.

Without additional explanation of what you're doing, I'm afraid I can't provide much more help.

Answer 2

If I understand you correctly you want to trigger an email ON the 71st birthday of a contact.

Issues:

1. Formulas do not cause a trigger or WFR to be executed
2. Unsure if a Time Based Workflow rule based on a field that is set to the 71st birthday date would fire if it is say 15 years in the future

With that said, The easiest way to do this would be to:

1. Have an Age Field on the Contact Record
2. Batch Class that runs through all contacts and calculates their age
3. WFR that sends an email when the age field changes to 71 checkbox is checked

Based on comment to send an email in the year they turn 71:

2b. Add a checkbox that indicates that the email should be sent

2c. Have the batch check that box if they will be 71 that year

Considerations:

To narrow the scope of the batch use the following in the criteria

1. Age field is blank

OR

2. Birthdate Month and day = Today (Month and day)

Using these filters would:

1. On first run, calculate all records since age is blank
2. On subsequent runs, only calculate for records who's brith month and year is the same as the current date. Thus limiting the scope of the records and reducing the batch size and execution time.

Bonus Exercise: use a WFR that whenever the Birthdate is populated or changes it populates the Age field. Takes the load off of the batch.