Apex Code Development You need to sign in to do that
Don't have an account?
Stephen Crane 7 Changing Business Hours To Minutes Cases
Hey all! I have a field that calculates the business hours from when a case comes in, to when it is first responsed. It looks like rihgt now, it displays the values in hours - I'm wondering if we can get minutes....not sure how to do that without messing everything up. The formula originally came from a success post that I can't find anymore plus some tinkering. Essentially our business hours are 9am EST - 9pm EST all week.
ROUND( 12.00 * (
( 7.00 * FLOOR( ( DATEVALUE( First_Response__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5.00,
MOD( DATEVALUE( First_Response__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 12 * ( MOD( First_Response__c - DATETIMEVALUE( '1900-01-08 13:00:00' ), 1 ) ) )
)
)
-
( 7.00 * FLOOR( ( DATEVALUE( CreatedDate ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( CreatedDate ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 12 * ( MOD( IF(AND(CreatedDate
< DATETIMEVALUE(TEXT(YEAR(DATEVALUE(CreatedDate
))) + '-' + TEXT(MONTH(DATEVALUE(CreatedDate
))) + '-' + TEXT(DAY(DATEVALUE(CreatedDate
))) + ' 13:00:00'), CreatedDate
>= DATETIMEVALUE(TEXT(YEAR(DATEVALUE(CreatedDate
))) + '-' + TEXT(MONTH(DATEVALUE(CreatedDate
))) + '-' + TEXT(DAY(DATEVALUE(CreatedDate
))) + ' 01:00:00')),DATETIMEVALUE(TEXT(YEAR(DATEVALUE(CreatedDate
))) + '-' + TEXT(MONTH(DATEVALUE(CreatedDate
))) + '-' + TEXT(DAY(DATEVALUE(CreatedDate))) + ' 13:00:00'), CreatedDate
) - DATETIMEVALUE('1996-01-01 13:00:00'), 1) ) )
)
)
),
MOD((First_Response__c - DATETIMEVALUE('2017-01-02 13:00:00'))*1440,1440)/60 )
ROUND( 12.00 * (
( 7.00 * FLOOR( ( DATEVALUE( First_Response__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5.00,
MOD( DATEVALUE( First_Response__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 12 * ( MOD( First_Response__c - DATETIMEVALUE( '1900-01-08 13:00:00' ), 1 ) ) )
)
)
-
( 7.00 * FLOOR( ( DATEVALUE( CreatedDate ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( CreatedDate ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 12 * ( MOD( IF(AND(CreatedDate
< DATETIMEVALUE(TEXT(YEAR(DATEVALUE(CreatedDate
))) + '-' + TEXT(MONTH(DATEVALUE(CreatedDate
))) + '-' + TEXT(DAY(DATEVALUE(CreatedDate
))) + ' 13:00:00'), CreatedDate
>= DATETIMEVALUE(TEXT(YEAR(DATEVALUE(CreatedDate
))) + '-' + TEXT(MONTH(DATEVALUE(CreatedDate
))) + '-' + TEXT(DAY(DATEVALUE(CreatedDate
))) + ' 01:00:00')),DATETIMEVALUE(TEXT(YEAR(DATEVALUE(CreatedDate
))) + '-' + TEXT(MONTH(DATEVALUE(CreatedDate
))) + '-' + TEXT(DAY(DATEVALUE(CreatedDate))) + ' 13:00:00'), CreatedDate
) - DATETIMEVALUE('1996-01-01 13:00:00'), 1) ) )
)
)
),
MOD((First_Response__c - DATETIMEVALUE('2017-01-02 13:00:00'))*1440,1440)/60 )
Just try it with First_Response__c = CreatedDate
Changing Business Hours To Minutes sounds a bit nonsensical for business hours.
Have you an example of date and the expected result?
You have to rewrite entirely the formula of course but it will be quite interesting to see the requirements for a such strange need.
The formula is working as intended, everything seems to give the correct time, and if a ticket is created off hours then the counter starts at zero when 9am hits. The only problem is they want to see it in minutes not hours (betwene CreateDate of the ticket, and the time of First Response)...and also Day light savings time.
Pretty sure I got the majority of this formula from: https://success.salesforce.com/answers?id=90630000000hy9ZAAQ
Would I just get rid of the /60?