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.