Apex Code Development You need to sign in to do that
Don't have an account?
bohemianguy100 Calculate Business Hours between created date and closed date
I'm trying to create a formula which calculates the business hours (8am - 5pm) between a task being created and closed.
I created a custom "ClosedDate" field on the task object.
I found an example online: http://help.salesforce.com/apex/HTViewSolution?id=000089863&language=en_US
I tested creating a task today (Oct. 28) and closing it with a date of Oct. 30th. The number of hours shown the formula field displayed 36 hours. It should only show the business hours between 8am and 5pm and exclude weekends. Is there a way to adjust the formula in the link above to accommodate those requirements?
Thanks for any help!
Here are some more examples:
http://stackoverflow.com/questions/11126378/salesforce-formula-calculate-business-hours-between-two-dates
HTH
Prabhan
Finally figured it out.
1) The times are based off GMT(0). An 8 Hour working day
2) 08:30:00am (Start of the working day) *All of them should be the same
8 *( (5*FLOOR(( DATEVALUE( IF(ISNULL(SlaExitDate), NOW(), SlaExitDate) ) -DATE(1996,01,01))/7) + MIN(5, MOD(DATEVALUE( IF(ISNULL(SlaExitDate), NOW(), SlaExitDate) )-DATE(1996,01,01), 7) + MIN(1, 24/ 8 *(MOD( IF(ISNULL(SlaExitDate), NOW(), SlaExitDate) -DATETIMEVALUE('1996-01-01 08:30:00am'), 1))) )) - (5*FLOOR((DATEVALUE( SlaStartDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(SlaStartDate)) & " 08:30:00am") - SlaStartDate,0) )-DATE(1996,01,01))/7) + MIN(5, MOD(DATEVALUE( SlaStartDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(SlaStartDate)) & " 08:30:00am") - SlaStartDate,0) )-DATE(1996,01,01), 7) + MIN(1, 24/ 8 *(MOD( SlaStartDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(SlaStartDate)) & " 08:30:00am") - SlaStartDate,0) -DATETIMEVALUE('1996-01-01 08:30:00am'), 1))))) )e.g - 0.75 hours = 45 Minutes.
I created another formula which handles this
TEXT(FLOOR(SLA_Duration__c)) + ':' + LPAD(TEXT(ROUND(MOD(SLA_Duration__c * 3600,3600)/60,0)), 2, '0')
returns "0:45"
BUT!!!
Because Salesforce has limits on their Formula FIelds
"Error: Compiled formula is too big to execute (9,021 characters). Maximum size is 5,000 characters"
I've had to split the first formula into two parts.
Let me know if this is what you want and I'll create a small Package for you to install
Note:
This does require Entitlement Management to be enabled.
Thanks for the above formula to calculate the business hours. It works and shows on the records. However, when I try to pull a report on Cases - the field value shows up as "Error", please can you let me know how to fix that? The sooner I hear from you, the better.
Thanks,
Mia.
Hi Mia Mia,
I tried running a report and didn't receive any issues.
Wanna perhaps provide:
Entitlement StartTime
Entitlement EndTime
SLA Duration Start
I don't have any entitlement process set up yet. Its just that there are two date/time fields. One of them is the Created Date.
There is another number field where I captured the business hours M-F based upon a 11 hour time schedule (as per your formula). It works fine on the records but it shows as "Error!" on the reports. Is that because we are trying to change the Date/Time formula into Text?
Hi Mia Mia,
I logged a call with SalesForce a while back and it seemed the formula was too complex for reports.
Therefore I needed to split the formula in two parts as the Start Time doesn't change, or requires a dml operation.
This package should resolve your issue: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t580000007D7d
Below is my formula
8 *( (5*FLOOR(( DATEVALUE( IF(ISNULL(Now()), NOW(), Now()) ) -DATE(1996,01,01))/7)
+
MIN(5,
MOD(DATEVALUE( IF(ISNULL(Now()), NOW(), Now()) )-DATE(1996,01,01), 7) +
MIN(1, 24/ 8 *(MOD( IF(ISNULL(Now()), NOW(), Now()) -DATETIMEVALUE('1996-01-01 08:30:00am'), 1)))
))
-
(5*FLOOR((DATEVALUE( CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate )) & " 08:30:00am") - Now(),0) )-DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE( CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate )) & " 08:30:00am") - CreatedDate ,0) )-DATE(1996,01,01), 7) +
MIN(1, 24/ 8 *(MOD( CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate )) & " 08:30:00am") - CreatedDate ,0) -DATETIMEVALUE('1996-01-01 08:30:00am'), 1)))))
)
@Rahul Jain 114,
The formula is too complex for reporting, you need to split it.
I've done so in this package
https://login.salesforce.com/packaging/installPackage.apexp?p0=04t580000007D7d
I removed the "am" from "08:30:00am". It Worked fine for me.
Regards,
@Rahul,
https://help.salesforce.com/articleView?id=formula_using_date_datetime.htm&type=0
The following explains that Salesforce will always use GMT(0) to perform the calculation then converts to User.Local.
Therefore you just need to ask yourself.
What time is it in GMT(0) when it's x time in my current time-zone?
@K1ngKon
Im really hoping you can assist me. I have a formula which calulates the "age" of an case and displays it like this (Case Age:12 day(s) 3 hrs 17 min). The issue I have is, it doesnt discount non business hours or days like yours. Its a long shot but is there any chance you could have a look at it for me and combine your formula and mine? :)
Thanks John
IF(IsClosed, Text(FLOOR(ClosedDate - CreatedDate)) &" "&"day(s)"&" "&Text(FLOOR(MOD( (ClosedDate - CreatedDate) * 24 ,24)))
&" "&"hrs"&" "&Text( FLOOR(MOD(((ClosedDate - CreatedDate) * 24)*60 ,60)))
&" "&"min", Text(FLOOR(NOW() - CreatedDate))
&" "&"day(s)"&" "&Text(FLOOR(MOD( (NOW() - CreatedDate) * 24 ,24)))
&" "&"hrs"&" "&Text( FLOOR(MOD(((NOW() - CreatedDate) * 24)*60 ,60)))&" "&"min")
Does This formula need to be changed acording to daylight savings or would it give correct result throughout the year?