Rounding Datetime Subtractions to the Quarter Hour in Salesforce Formulas

I’m proud of this one, so you all get to read about it.  As part of a study group I’m in at the moment, I was given an assignment to set up a little app in a Salesforce developer org that includes a time logging component.  I found myself wanting to subtract two datetime fields, display the result out to the hundredths place, and round to the nearest quarter hour.  I searched the Internet a bit looking for a formula that would do the job, but didn’t stumble across anything.  So, I decided to spend a little time and see if I could figure it out myself.

So, I knew I wanted to start with something like this:

(End_Date_Time__c - Start_Date_Time__c) * 24

That will give us the number of hours between the start datetime and end datetime.  But, I would get values like 2.27 if a session ran a minute or two over two hours and fifteen minutes.  I realized I needed to do something with a modulus function and handle the remainders.  I got myself a little confused, though, mainly around two points:

1. If you want to use a decimal value as the divisor in the MOD() function in Salesforce’s formula language, make sure you preface your “.” with a “0.”  So, “0.25” will work, but “.25” will not.
2. Remember that, when you use the modulus function, the remainders you get back are in “units,” if you will, of your divisor.  So, in my case, I got a little confused because I was forgetting that the returned values were going to be between 0 and 0.25.

After a little experimenting, I realized that the following formula would give me results rounded DOWN to the quarter hour:

(End_Date_Time__c - Start_Date_Time__c) * 24) - MOD((End_Date_Time__c - Start_Date_Time__c) * 24,0.25)

That formula basically takes the hour value to two decimal places, and subtracts the remainder obtained by taking the same value and dividing it by 0.25.  When I figured that out, I thought, “Well, for my imaginary company in my exercise, maybe they always want to round down to the quarter hour.”  But, that seemed like taking the easy way out, so I sat down to do the little bit of extra thinking for rounding to the NEAREST quarter hour.

Since the remainders coming back were values between 0 and 0.25, I knew I wanted to round down if the value was less than 0.125 (the “half-way mark”), and round up if the value was greater than or equal to 0.125.  So, I added an IF() function to frame the two possibilities, and used the formula from above as the case if the remainder was less than 0.125.  Then, for the “else” case in my IF(), I realized that for the “round up” side of the formula, the remainder represented how much of the next quarter hour had been worked by the staff person.  So, by subtracting that remainder from 0.25, I get the amount I need to ADD to the datetime subtraction to correctly round up to the next quarter hour.

The final formula looks like this:

IF(
MOD((End_Date_Time__c - Start_Date_Time__c) * 24,0.25) < 0.125,
((End_Date_Time__c - Start_Date_Time__c) * 24) - MOD((End_Date_Time__c - Start_Date_Time__c) * 24,0.25),
((End_Date_Time__c - Start_Date_Time__c) * 24) + (0.25 - MOD((End_Date_Time__c - Start_Date_Time__c) * 24,0.25))
)

I hope someone out there gets some use out of this.  I figure I can’t be the only person using Salesforce who wants to round to the nearest quarter hour with a formula.

I’ve Gone Pokémon on Trailhead

I love Trailhead.  Salesforce has applied gamification to learning their platform, and it’s brilliant.  Billed as, “the fun way to learn Salesforce,” on the Trailhead site, users can pursue learning Trails, made up of Modules and Projects, that tailor the learning experience to various domains by which one can filter content.   For example, you can select “Admin” and “Beginner” in the filter list, and start going through all the training modules tagged as such.   I’m doing that right now, as I’ve begun preparation for the Admin 201 certification exam.  I’ve decided to pursue the Administrator certification track at this point.  It’s been a little over a year since I completed the Dev 501 process, and I’m ready to get back into the certification effort (I needed a break after that programming assignment).

But, here’s why I say, “I’ve Gone Pokémon on Trailhead:”  I’ve “gotta catch ’em all!”  Giving out these badges in the gamified context is a great motivator for me, it seems.  It makes cracking open these Trails, Modules, and Projects on Trailhead a lot of fun!  That’s especially helpful since a lot of the content I’m going through right now is review.  I’ve been using Salesforce for about 4 years now, and I’ve gotten familiar with a good bit of the admin functionality.  That said, I’m learning something in almost every Trailhead module I complete!  For instance, I set up Account Teams and used Contact Roles in the “Accounts & Contacts” module this morning.  My company doesn’t use those features extensively, so I’d never had reason to play with them.  And, after finishing that experience…BADGE!

I’ve also got a project for work right now in which I’m probably going to have to figure out an integration with an external partner, and I want to use Apex web services to tailor GET / PUT requests for RESTful web service calls between us and the partner.  Recently, a new “Apex Integration Services” module showed up on Trailhead.  I ran through it, got a great refresher course on using SOAP and RESTful web services for the Salesforce API,and also how to write my own web services with Apex.  And then?  BADGE!

Oh, the joy!  The endorphin rush!!  I love getting Trailhead badges!!!

I discovered Trailhead at Dreamforce last year.  Since, I’ve learned it’s a great resource, and I’m trying to motivate my team to take advantage of  this free training option.  If you haven’t experienced Trailhead yet, get over to https://developer.salesforce.com/trailhead and try it out!