After a few weeks without a new blog post, here is our second in quick succession! But I must confess this one did not take long to put together, as the topic I want to share with you today is something I’ve been looking into for a while now: adding months to a date in a Salesforce formula field.
A common use case for formula fields in Salesforce is to take a date field and another field representing a duration of some kind, and add the two to give a future date. Perhaps a Quote Valid Until Date, or a custom Contract End Date.
Adding years to a date is pretty straightforward, since taking a year and adding a number to it will return another year. Adding days to a date is even easier – just say how many days you want to add on and all the date maths will be taken care of – month lengths, year changes, even leap years. A piece of cake!
But adding months is trickier in a formula field. You can’t just add an integer to a month number, since November plus 3 months would be 10 +3 = 13, and there isn’t a 13th month, so you get an error.
Often you’ll see people settle for just adding a multiple of 30 days, for example Date + 90 for 3 months. But that’s an estimate at best – not every month has 30 days so even though it might be ‘good enough’, it’s not right. And it wouldn’t take long for a customer to pick up on that inaccuracy.
So I spent a while hunting around for the solution – a way of getting an accurate result.
First stop was the trusty Common Date Formulas help article. This does a decent job of explaining the theory behind the calculation, but for me the formula wouldn’t even compile within the character limit. Your mileage may vary, but I suspect the frequent repetition of YEAR() and MONTH() functions probably isn’t very kind on the behind-the-scenes compile limits.
So next I turned to Google which, in turn, led me to some blogs and some previous answers on both the Success community and the developer boards. Some of these were very good and seemed to get the months aspect pretty much nailed. But though they passed the years and months test, they often fell down on the leap year test, with the occasional February 29th catching the code out.
Which brings us to our silver medallist – Ankit Tenaja – who gets us around the leap year problem, but does so by saying that February dates can only ever get as high as the 28th. Although this means we have a formula that is only inaccurate for one day in every four years, it’s not quite the perfection we desire!
So on we go, until we find a Developer forum answer from a user named only as ben_sda. I haven’t had any luck finding ben_sda – who probably isn’t even called Ben judging by their sign-off of ‘A’ – so if you are that developer, or you know them… Thank you! This formula does it all – returning not just the year and month but also taking everything into account when returning a day value, including those pesky leap years. In my eyes, we have a winner! And here it is:
DATE (
/*YEAR*/
YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c) + Months_Duration__c - 1)/12),
/*MONTH*/
CASE(MOD(MONTH(Start_Date__c) + Months_Duration__c, 12 ), 0, 12, MOD(MONTH(Start_Date__c)+ Months_Duration__c, 12 )),
/*DAY*/
MIN(DAY(Start_Date__c),
CASE(MOD(MONTH(Start_Date__c) + Months_Duration__c,12), 9, 30, 4, 30, 6, 30, 11, 30, 2,
/* return max days for February dependent on if end date is leap year */
IF(MOD(YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c) + Months_Duration__c)/12), 400) = 0 || (MOD(YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c) + Months_Duration__c)/12), 4) = 0 && MOD(YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c) + Months_Duration__c)/12), 100) <> 0
),
29,28),
31))
)
But can you do any better? Can you spot anything ben_sda has missed? Or perhaps you just think a formula of this length is sub-optimal and that a standard ADDMONTHS() function should exist in formulas natively, like it does in Apex. (If so, maybe give this idea a vote.)
Whatever your thoughts, we’d love to hear them! …What do we want? Your thoughts! When do we want them? NOW() + 0, of course!