function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
huskerwendyhuskerwendy 

Business Hours Between Two Dates/Times returns negative number

I found this formula referenced by a few people:

https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US

I'm using it to find the business age between a custom date time field called First Contact Date and the Case Created Date. We want a 12 hour day starting at 7:00 AM. Everything works fine except when the date created is between midnight and 7:00 AM. When the date created is between those hours, the value returned in the formula is a negative number. How can I fix this? I created test datetime fields so that I can test the formula with different values. Here's the formula using the test fields. Any advice?

ROUND(12*(
(5*FLOOR((DATEVALUE(Test_First_Contact_Date__c)-DATE(1996,01,01))/7) +
MIN(5,
    MOD(DATEVALUE(Test_First_Contact_Date__c)-DATE(1996,01,01), 7) +
    MIN(1, 24/12*(MOD(Test_First_Contact_Date__c-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(Test_DateCreated__c)-DATE(1996,01,01))/7) +
MIN(5,
    MOD(DATEVALUE(Test_DateCreated__c)-DATE(1996,01,01), 7) +
    MIN(1, 24/12*(MOD(Test_DateCreated__c-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
), 2)

Best Answer chosen by huskerwendy
Bryan Zelinski 4Bryan Zelinski 4
This came up recently on a project for our purposes as well.

I know this is a formula question, but I would suggest using a Case Apex Trigger that utilizes the Business Hours object under Setup-->Administer-->Company Profile-->Business Hours Object.

You would also be able to account for Holidays and other "off-time".

Let me know if you want to go down this route.

Overall footprint of the code is probably 10 lines.

All Answers

James LoghryJames Loghry
Hi Wendy, I believe you just need to swap your references of "Test_First_Contact_Date__c" with the references of "Test_DateCreated__c".  In subtraction terms, Test_DateCreated__c should be the minuhend, and Test_First_Contact_Date__c should be the subtrahend, since Test_DateCreated__c is the most recent date / highest value.
James LoghryJames Loghry
Oh, and if husker is a Nebraska Cornhusker reference, then go Cyclones ;-)
huskerwendyhuskerwendy
Hi James,
Thanks for the reply but that's not the problem. I tried it and it makes all the values that were correct negative but it did fix the problem with the negative values. The problem is only happeneing when the values are between midnight to before 7:00 AM.

Husker is a reference to the Nebraska Cornhuskers. Go Big Red! :)
Forza di SognoForza di Sogno
Would adding an ABS (absolute reference) help?
Bryan Zelinski 4Bryan Zelinski 4
This came up recently on a project for our purposes as well.

I know this is a formula question, but I would suggest using a Case Apex Trigger that utilizes the Business Hours object under Setup-->Administer-->Company Profile-->Business Hours Object.

You would also be able to account for Holidays and other "off-time".

Let me know if you want to go down this route.

Overall footprint of the code is probably 10 lines.
This was selected as the best answer
huskerwendyhuskerwendy
Thanks, Bryan. I may do that. I think the correct way to do it would be to use a trigger but recently I installed Public Knowledge from the appexchange and I can't deploy any new triggers because of test failures in that app that I've got to figure out and fix. Since I needed to get this proejct done, I went the formula/workflow rule/hack field method. In order to get the formula to work, I created a new field called "Date Created - Case Age" and created a workflow rule to populate it to set the time to 7:00 AM if the case comes in between midnight and 7:00 AM otherwise it's populated with the CreatedDate. I'm using the new field in this formula rather than the CreateDate field.
Bryan Zelinski 4Bryan Zelinski 4
Well at least you got it to "work" and good luck with fixing the test failures. One nice thing working with the BusinessHours class you can target BusinessHours.diff() function and get down to the millisecond if need be. If you need help on the case trigger and/or class. Just let me know!
Crystal CanalesCrystal Canales
Hi Wendy, 

Would you mind sharing the workflow formulas you used for your work-around to prevent the negative calculations? I'm struggling with the same issue but unfortunately still have limited knowledge around creating formulas. This would be a great help!
Thank you!
huskerwendyhuskerwendy
Hi Crystal,

What I ended up doing is creating a field called "Date Created - Case Age". That field is populated via a workflow rule. I couldn't use a formula for the field because I am using this field in the formula that calculates the age and the formula size was too large. However, I'm doing more my case age formula than you probably are so you may be able to make it a formula field and not a workflow rule. If I were you, I would try to create the field as a formula first. If it's too large, then create the workflow rule.

The workflow rule Evaluation Criteria is: when a record is "created, and any time it's edited to subsequently meed criteria"
Rule Criteria = Case: Date Created - Case Age equals ""

Use a Formula to update the field. Here's the formula:

CASE(FLOOR ( MOD( CreatedDate-($System.OriginDateTime + 5/24),1) *24) ,
0, (CreatedDate+ 0.2916),
1, (CreatedDate+ 0.25),
2, (CreatedDate+ 0.208),
3, (CreatedDate+ 0.1667),
4, (CreatedDate+ 0.125),
5, (CreatedDate+ 0.084),
6, (CreatedDate+ 0.042),
CreatedDate)

The system.OriginDateTime value is in UTC time. The "+ 5/24" is the time difference between my time zone (CDT) and UTC time. When daylight savings time ends, I'm going to need to change this formula to say "+ 6/24". That is one of the big problems with the way this whole thing works. It would be really easy to forget to do that when DST ends. Now use that field in the Case Age formula rather than the date_created.
 
Greg MerrittGreg Merritt
This can also be solved by rounding any time after midnight but before business hours up to be the beginning of business hours. In your example, you could replace Test_DateCreated__c-DATETIMEVALUE('1996-01-01 12:00:00') with:

IF(AND(Test_DateCreated__c < DATETIMEVALUE(TEXT(YEAR(DATEVALUE(Test_DateCreated__c))) + '-' + TEXT(MONTH(DATEVALUE(Test_DateCreated__c))) + '-' + TEXT(DAY(DATEVALUE(Test_DateCreated__c))) + ' 12:00:00'),Test_DateCreated__c >= DATETIMEVALUE(TEXT(YEAR(DATEVALUE(Test_DateCreated__c))) + '-' + TEXT(MONTH(DATEVALUE(Test_DateCreated__c))) + '-' + TEXT(DAY(DATEVALUE(Test_DateCreated__c))) + ' 00:00:00')),DATETIMEVALUE(TEXT(YEAR(DATEVALUE(Test_DateCreated__c))) + '-' + TEXT(MONTH(DATEVALUE(Test_DateCreated__c))) + '-' + TEXT(DAY(DATEVALUE(Test_DateCreated__c))) + ' 12:00:00'),Test_DateCreated__c) - DATETIMEVALUE('1996-01-01 12:00:00')

Basically this says: If the time is before 12:00:00 GMT and on/after 00:00:00 GMT then return 12:00:00 of that day. If it's not before 12 and after 0, then return the time as usual. Yes, this is certainly a bit hack-y, but it gets the job done.
MikeyJamJamsMikeyJamJams
Thanks, Greg. Your solution works as well. We recently ran into this issue where a negative number was being returned when we tried to find the difference between the current date/time and a date/time field on our Case. The formula provided by Salesforce worked, except when the time on our date/time field was between 12:00 AM and 8:00 (the time our business hours start). Using the IF statement allowed us to get around this.