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
DekorDekor 

Business hours between created date and now

I'm trying to create a field that displays to my operators how much time is remaining before a case escalates.  My plan was to have a field that calculates how many business hours have passed since the date/time the case was created and then subtract this figure from the escalation hours. 

So for example, a priority 1 case in our system has a escalate time if not closed of 0.5 (30 minutes) so subtract from that the time open calculated by my business hours field.

Here is an example of the formula I'm after but the problem is this is designed to calculate between two fields, I want to calculate between a field and the current date/time.  I thought replacing "YourEnd/RecentDateFieldHere" with NOW () would do it but the formula doesn't seem to work.  Any ideas?

  
ROUND( "NumberOfBusinessHoursWithinaDay" *( (5*FLOOR(( DATEVALUE( "YourEnd/RecentDateFieldHere" ) -DATE(1996,01,01))/7) +MIN(5, MOD(DATEVALUE( "YourEnd/RecentDateFieldHere" )-DATE(1996,01,01), 7) +MIN(1, 24/ "NumberOfBusinessHoursWithinaDay" *(MOD( "YourEnd/RecentDateFieldHere" -DATETIMEVALUE('1996-01-01 13:00:00'), 1)))))
-
(5*FLOOR((DATEVALUE( "YourStart/OldDateFieldHere" )-DATE(1996,01,01))/7) +MIN(5, MOD(DATEVALUE( "YourStart/OldDateFieldHere" )-DATE(1996,01,01), 7) +MIN(1, 24/ "NumberOfBusinessHoursWithinaDay" *(MOD( "YourStart/OldDateFieldHere" -DATETIMEVALUE('1996-01-01 13:00:00'), 1)))))), 0) / "NumberOfBusinessHoursWithinaDay"


 
YuchenYuchen
Your formula looks fine to me. So what is the error that you are getting?
DekorDekor
Here is the completed code.  It isn't given me any syntax errors when I create the field however the field is reporting incorrectly.  For example on a case where the created date/time is 23/03/2015 15:00 it is calculating as 14.83
  
Decimal Places	2	 	 
ROUND( 12 *( (5*FLOOR(( DATEVALUE( now () ) -DATE(1996,01,01))/7) +MIN(5, MOD(DATEVALUE( now () )-DATE(1996,01,01), 7) +MIN(1, 24/ 12 *(MOD( now () -DATETIMEVALUE('1996-01-01 13:00:00'), 1)))))
-
(5*FLOOR((DATEVALUE( CreatedDate )-DATE(1996,01,01))/7) +MIN(5, MOD(DATEVALUE( CreatedDate )-DATE(1996,01,01), 7) +MIN(1, 24/ 12 *(MOD( CreatedDate -DATETIMEVALUE('1996-01-01 13:00:00'), 1)))))), 0) / 12

 
DekorDekor
Also, the field is doesn't seem to be updating.
Jordan BergesonJordan Bergeson
Hi Dekor, were you ever able to figure this out? Thanks!