Woohoo, it’s February!!
It’s worth celebrating making it through January in any year, though 2021 has been particularly tough with the various pandemic restrictions in place around the world. Secondly, have you seen the calendar? If your diary is arranged to start the week on a Monday then your calendar will be aesthetically sublime for February 2021. Seriously, I’m inordinately pleased about this!!
There is one headache to the shortest month in the year though, and that’s managing subscriptions that renew on the dates not present in February. We’re going to address that and resolve this problem in Salesforce so it need never worry you again.
The market for subscription based products has been exploding for a number of years, expanding from digital industries to all manner of services and physical products too.
There are many ways to model this repeat income in your Salesforce Org, though somewhere along the line it is likely you will need to calculate a date one month in advance from another given date, and to do so recursively, without knowing the values that came before i.e. setting the Close Date of a renewal Opportunity whilst only knowing the Close Date of the current Opportunity.
Despite seeming like a simple requirement, any amount of experience manipulating dates with formulae will teach you it’s not!
Since Spring ‘18, Salesforce provides the ADDMONTHS function:
On the face of it this would appear to provide a clean solution. However, subscription dates occurring at the end of the month will incur a problem whereby the actual subscription date degrades to earlier in the month, ultimately to the 28th whenever it circles round to February.
If you have a subscription date of the 30th of the month then:
ADDMONTHS(January 30th, 1) = February 28th
ADDMONTHS(February 28th, 1) = March 28th
Notice this is NOT March the 30th, which is the desired subscription date!
So how do you revert back to a later date after going through February (or any other shorter month)?
After recent projects working with Stripe, we’ve settled on borrowing the concept of what they call the Anchor Date of the subscription.
The Anchor Date is not a full date, rather it is the day number of the month (1-31) that the subscription renews on. If that number exists in a month then that is when the renewal will occur, and if it doesn’t then it will instead happen on the last day of the month.
How do we put this into practice?
Firstly, we need a number field on the relevant record to save the Anchor Date in.
We then need a simple way to establish what the last day number of the next month is.
Borrowing from the Sample Date Formulas we can springboard from ‘Find the Last Day of the Month’, which says ‘The easiest way to find the last day of a month is to find the first day of the next month and subtract a day’;
DATE( YEAR ( date ), MONTH ( date ) + 1, 1 ) – 1
Now to avoid issues with handling a new year and dealing with leap years we can leverage ADDMONTHS;
DATE( YEAR( ADDMONTHS( date, 2 ) ) , MONTH( ADDMONTHS( date, 2 ) ), 1 ) - 1 )
Notice we’re adding 2 months, not 1. If we only added 1 month we’d get the last day of the month of the current value of the date field, whereas we want the last day of the next month. Also note that this formula returns a date. We’ll need to wrap it in DAY() to enable a comparison against the Anchor Date field we have created.
All that’s left is to use a simple IF statement to determine how to set the new date
IF( Anchor Date > Last Day of Target Month,
Set date to Last Day of Target Month, //Value if TRUE
Set date to Anchor Date in Target Month //Value if FALSE
)
Putting it all together:
IF(
AnchorDate > DAY(DATE(YEAR(ADDMONTHS(date,2)), MONTH(ADDMONTHS(date,2)),1)- 1),
DATE(YEAR(ADDMONTHS(date,2)),MONTH(ADDMONTHS(date,2)),1) -1,
DATE(YEAR(ADDMONTHS(date,1)),MONTH(ADDMONTHS(date,1)),AnchorDate)
)
So there you have it, a clean solution using only one custom field that stores your Anchor Date – the day number of the month (1-31) that the subscription renews on – and an IF statement that handles setting the next date from the current date, whilst respecting the length of whatever the next month is, whether it’s going to be a new year or whether it’s currently a leap year.
Back to celebrating it being February again – Whoop!! Whoop!!
Photo by Adam Whitlock on Unsplash
Our independent tech team has been servicing enterprise clients for over 15 years from our HQ in Bristol, UK. Let’s see how we can work together and get the most out of your Salesforce implementation.
