Looking for a date? Salesforce can help

For this week’s post, I wanted to get back into our favourite How To category and come up with something that will be of practical use to you all. So my topic this week is dating.

Because dating is something we all do, isn’t it? Whether we’re old or young, rich or poor, married or single, we all find ourselves searching for a date sometimes.

Sorry, I should clarify. When I say ‘dating’, I mean the act of ascertaining the date of an event. You didn’t think I meant any other kind of dating, did you? Although, if you are interested in learning how Salesforce can help you in that area too, you should definitely check out this classic Michaelforce video.

So, yes, I want to talk about how we can calculate dates in Salesforce. And if you’ve been a reader of this blog since the start, you might recognise that topic. Because way back in early 2015, we published an article entitled How To: Build a Formula to Add Months to a Date.

It’s a fairly common scenario in a CRM, or even in a database of any kind. Let’s say you have a Contract Start Date field and a Contract Duration field. You might want to dynamically calculate the Contract End Date using a formula field.

And that can get trickier than it sounds pretty quickly. Let’s say you have a one-month contract starting on January 30th. Best of luck finding February 30th on your calendar. Or how about a 12-month contract which goes live during a leap year on February 29th? That date isn’t going to exist the following year. Even something as simple as a six-month contract starting in the tenth month of the year isn’t all that simple, as adding 6 to 10 gives you 16, and – I hate to break it to you – there isn’t a sixteenth month of the year.

So, our 2015 article shared a clever formula (I’m allowed to say that; I didn’t write it) which took all of these calendar quirks into consideration, and gave you a nice, reliable way of calculating a resulting date when adding any number of months to any original date. Perfect. Job done. So why am I writing a new blog about this?

Well, amongst the many goodies delivered to us in Spring ‘18 was a hidden gem that I want to shine a light on. It isn’t a marquee feature. Its page in the release notes doesn’t even contain a single image to show it off. And it certainly isn’t going to help Salesforce get to $20bn all on its own. But it might just make a difference to any org that needs to calculate a date based on adding months. And, as we’ve discussed, that’s actually a hell of a lot of orgs.

It is therefore my pleasure to introduce you to… the ADDMONTHS formula. Yes, more than three years after we shared a workable – but complex – formula right here on this blog, Salesforce have finally delivered us a simple, neat – and VERY concise – way to achieve the same thing.

How concise? Well, check out this comparison.

Given two sample fields, Start_Date__c (datatype: Date) and Duration__c (datatype: Number), the original formula churns through 4,083 characters of compiled calculations, and ends up looking like this:

DATE ( 

/*YEAR*/ 

YEAR (Start_Date__c) + FLOOR ( (MONTH ( Start_Date__c ) +  Duration__c - 1)/12), 

/*MONTH*/ 

CASE ( MOD ( MONTH ( Start_Date__c )+Duration__c, 12 ),0,12,MOD ( MONTH ( Start_Date__c )+Duration__c, 12 )), 

/*DAY*/ 

MIN ( DAY ( Start_Date__c ), 

CASE ( MOD ( MONTH ( Start_Date__c )+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 ) + Duration__c)/12) , 400 ) = 0 || ( MOD ( YEAR ( Start_Date__c ) + FLOOR ( (MONTH ( Start_Date__c ) + Duration__c)/12) , 4 ) = 0 && MOD ( YEAR ( Start_Date__c ) + FLOOR ( (MONTH ( Start_Date__c ) + Duration__c)/12) , 100 ) <> 0 

) 

, 29,28) 

,31 ) ) 

)

Whereas the new function uses just 68 characters and results in the much more eye-pleasing formula below:

ADDMONTHS(Start_Date__c, Duration__c) 

Look at it. Isn’t it just beautiful?

But, before you dive into your org and start trying this one out, a quick thing to note. There is a slight difference between how the original formula and the new function work, and it’s to do with month ends.

Let’s say you have a Start Date of February 28th and a duration of 6 months. The old formula would spit out an End Date of August 28th, and that’s probably what you’d expect. The new one, however, gives you August 31st. That is to say that it treats the last day of a month as a relative rather than an absolute, so if you input the last day of a month, it will output the last day of another month, and the numbers of those two days may differ – since the number of days in a month sometimes differs. So depending on how you wish to treat month ends, this is just something to consider when choosing either the old or the new formula.

Bonus Feature!

Oh, one other thing to leave you with before I sign off. Because ADDMONTHS wasn’t the only date-based treat that Salesforce gave us in the Spring release.

How about a WEEKDAY function to return a numerical day of the week from a date? Confused what that means? Well it’ll return 1 if the input date is a Sunday (because Americans are weird like that and think that part of the weekEND should go at the very start of the week; don’t ask me why), 2 for Monday, etc etc, all the way through to 7 for Saturday.

Why’s that useful? Well, one example is if you wanted to work out whether a date was going to fall on the weekend or not. (Maybe you have a reminder email that goes out one day before something expires. You probably don’t want to send it on a Saturday if the expiry is on the Sunday, as likely no-one will see it, so you may have a formula which sends the reminder two or three days earlier in that scenario.)

And why is the new method an improvement on what we had before? Because previously you had to pick some arbitrary date in the past which you knew the weekday of, then work out how many days had passed between that historic date and your date, and finally use the MOD function on that difference. Fiddly, confusing, and unnecessarily complicated. You’ll see what I mean below:

CASE(
   MOD( Your_Date__c - DATE( 1900, 1, 7 ), 7 ),
   0, "Sunday",
   1, "Monday",
   2, "Tuesday",
   3, "Wednesday",
   4, "Thursday",
   5, "Friday", 
   "Saturday"
 )

In comparison, this new function is a dream – and a vision in simplicity. Check it out:

WEEKDAY(Your_Date__c)

So thank you, Spring ‘18, for these two little (but extremely valuable) goodies!