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
Austin GutzAustin Gutz 

Business Hours Formula Issue

I found a formula on the community to use to calculate the buiness hours between two date.

The formula works perfect... unless the created date is before our business hours.
*Business hours: 8am-5pm EST(M-F)

User-added image
  
IF( 

(((Assigned_Time__c - CreatedDate)*1440) / 60) < 0.1, 0, 

9 *( (5*FLOOR((DATEVALUE(IF(ISNULL(Assigned_Time__c), Assigned_Time__c, Assigned_Time__c)) - DATE(1996,01,01))/7) 
+ 
MIN(5, 
MOD(DATEVALUE(IF(ISNULL(Assigned_Time__c), Assigned_Time__c, Assigned_Time__c)) - DATE(1996,01,01), 7) + 
MIN(1, 24/ 9 *(MOD(IF(ISNULL(Assigned_Time__c), Assigned_Time__c, Assigned_Time__c) - DATETIMEVALUE('1996-01-01 14:00:00'), 1))) 
)) 

- 

(5*FLOOR((DATEVALUE(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 14:00:00") - CreatedDate,0)) - DATE(1996,01,01))/7) + 
MIN(5, 
MOD(DATEVALUE(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 14:00:00") - CreatedDate ,0)) - DATE(1996,01,01), 7) + 
MIN(1, 24/ 9 *(MOD(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 14:00:00") - CreatedDate ,0) - DATETIMEVALUE('1996-01-01 14:00:00'), 1))))) 
) 
)

 
Andrew GAndrew G
Hi
Looking at the code , and comparing to the help page version (https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm) , i figure the extra code is attempting to workout the start of the working day.
Given that it is the before start of day that is an issue, i would wonder if the timezone is causing an issue. For example, my start of working day is actually the evening before @ 10pm.
I had a quick play, simulating 9:30am as my start of day, hence 23:30 the prior evening (GMT).  The code below appears to resolve the start of day function, but I have not exhaustively tested the code. 
IF( 

(((LastModifiedDate - CreatedDate)*1440) / 60) < 0.1, 0, 

9 *( (5*FLOOR((DATEVALUE(LastModifiedDate) - DATE(1996,01,01))/7) 
+ 
MIN(5, 
MOD(DATEVALUE(LastModifiedDate) - DATE(1996,01,01), 7) + 
MIN(1, 24/ 9 *(MOD((LastModifiedDate-1) - DATETIMEVALUE('1996-01-07 22:30:00'), 1))) 
)) 

- 

(5*FLOOR((DATEVALUE(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate)) & " 22:30:00") - CreatedDate,0)) - DATE(1996,01,01))/7) + 
MIN(5, 
MOD(DATEVALUE(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate -1)) & " 22:30:00") - CreatedDate ,0)) - DATE(1996,01,01), 7) + 
MIN(1, 24/ 9 *(MOD(CreatedDate + MAX(DATETIMEVALUE(TEXT(DATEVALUE(CreatedDate-1)) & " 22:30:00") - CreatedDate ,0) - DATETIMEVALUE('1996-01-07 22:30:00'), 1))))) 
) 
)

Note that when comparing the date to determine start of Day , I have subtracted a day to get the prior evening's time (for GMT start of day).

Not sure if this would affect you, i can't fathom what timezone you are in and determine GMT change of day.

My other question was regarding your code  
IF(ISNULL(Assigned_Time__c), Assigned_Time__c, Assigned_Time__c)
It seems redundant as it indicates that IF Assigned Time is Blank, use Assigned Time else use Assigned Time.  However, not knowing your environment it may be valid for some reason.

HTH
Regards
Andrew