Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
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!

K1ngKon

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)))))
)
Shaun B.
Thanks Konrad!  Do you know how this can be modified to look at minutes of SLA between the two dates instead of just hours?
K1ngKon
Hi S.Bailey, this returns a Decimal representation of hours, "0.75"
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
Shaun B.
Yes!  That would be great!  Thanks!!
K1ngKon

Note:
This does require Entitlement Management to be enabled.
Mia Mia

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.
K1ngKon

Hi Mia Mia,

I tried running a report and didn't receive any issues.

Wanna perhaps provide:

Entitlement StartTime
Entitlement EndTime
SLA Duration Start

Mia Mia

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?
K1ngKon

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.

Rahul Jain 114
When i try to run the report using above formula - it gives ##Error  Any Luck ?  Or is there any way i can  find minutes as per business Hours

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)))))
)

K1ngKon

@Rahul Jain 114,

The formula is too complex for reporting, you need to split it.

I've done so in this package

Marcos Lira da silva
@Rahul Jain 114,

I removed the "am" from "08:30:00am". It Worked fine for me.

Regards,
Rahul Jain 114
How do i change the Timezone from GMT(0) to 2.30

@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?

John Haywood

@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")

RPal
@K1ngKon
Does This formula need to be changed acording to daylight savings or would it give correct result throughout the year?