Adding dates is tricky. Months have different numbers of days, so you can’t rely on just adding 30 days to get an extra month. You also can’t just add a certain number of months because formulas in Salesforce don’t auto increment the year. The solution is modular arithmetic and conditionals.
The goal here was to make a set of fields to send out emails on the first day of each month for 6 months, given a specific month to start with.
What I’m doing here is:
- Year: Figuring out the month number, adding one less than the number of months over all, dividing it by 12, and rounding down to add either a 0 or 1 to the year. You have to subtract one from the month because 12/12 = 1 and you don’t want December adding an extra year.
- Month: If the resulting month is December, return 12. Otherwise return the month number modulo 12. (12 mod 12 is 0, hence the conditional).
- Day: Always returning 01, the first day of the month.
DATE(
YEAR( date ) + FLOOR( ( MONTH ( date ) + number_of_months - 1 ) / 12 ),
IF( MONTH ( date ) + number_of_months = 12, 12, MOD( MONTH ( date ) + number_of_months, 12 )),
01
)
How to use this: the date variable should be the date field you are starting with. You should replace number_of_months with the number of months you want to add to the original date. If the date is 07/01/2017 I want this to go out on 08/01/2017, I’d set number_of_months to 1. If 09/01/2017, I’d set it to 2, etc.
Note: This only works for the first of each month. If you need it to work on any day of the month, use this more complicated solution to account for months having different lengths.
