You need to sign in to do that
Don't have an account?
Srinivasa Rao Balle 1
Case first touch business hours calculation with formula filed
Hi All,
I have one requirement of caliculate the business hours of case first touch by rep, for this i created 2 fields
1. First touch date (Date time filed)
2. Business Tat hours. (Formula filed)
when ever a case is changing the staus from New to other statuses i am updating first touch date with system.now() in trigger. then after i am using this formula to get the business hours calucation based on case created date and first touch date. here is the formula
ROUND( 8 * (
( 5 * FLOOR( ( DATEVALUE(First_Touch_Date__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE(First_Touch_Date__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8 * ( MOD( First_Touch_Date__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE(CreatedDate) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8 * ( MOD(CreatedDate - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
)
)
),
0 )
Now the issue is when a case is created between 12.00 AM to 8.30 AM. and first touch date is that case created date then business hours is caliculating negative values. here Case is opened at 8/9/2016 4.15AM and First touch date is 8/9/2016 4.53 PM then my Business Hours(TAT) should be 8 hours 23 minutes, but it is showing -3.00 hours as negative value with above salesforce provided formula. how should we reslove this with formula field..
here are my business hours.
How should i resolve this issue any help is appreciated.
Thanks
I have one requirement of caliculate the business hours of case first touch by rep, for this i created 2 fields
1. First touch date (Date time filed)
2. Business Tat hours. (Formula filed)
when ever a case is changing the staus from New to other statuses i am updating first touch date with system.now() in trigger. then after i am using this formula to get the business hours calucation based on case created date and first touch date. here is the formula
ROUND( 8 * (
( 5 * FLOOR( ( DATEVALUE(First_Touch_Date__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE(First_Touch_Date__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8 * ( MOD( First_Touch_Date__c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE(CreatedDate) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8 * ( MOD(CreatedDate - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
)
)
),
0 )
Now the issue is when a case is created between 12.00 AM to 8.30 AM. and first touch date is that case created date then business hours is caliculating negative values. here Case is opened at 8/9/2016 4.15AM and First touch date is 8/9/2016 4.53 PM then my Business Hours(TAT) should be 8 hours 23 minutes, but it is showing -3.00 hours as negative value with above salesforce provided formula. how should we reslove this with formula field..
here are my business hours.
How should i resolve this issue any help is appreciated.
Thanks
1) http://help.salesforce.com/HTViewSolution?id=000089863
2) http://stackoverflow.com/questions/11126378/salesforce-formula-calculate-business-hours-between-two-dates
A simple lightweight application can solve your issue of calculating the business hour, we have developed an Application called 'Business Hour Countdown Timer', which you can install from AppExchange through the link below:
https://appexchange.salesforce.com/listingDetail?listingId=a0N3000000DXzrTEAT
Employees get business hour information by countdown timer, with customizable Background and Timer-Text color.
For better understanding, you can also check our user guide manual http://webkul.com/blog/salesforce-business-hour-countdown-timer/
Feel free to contact us in case of any configuration issue.
Support: support@webkul.com
Ajit Kumar
Salesforce Consultant
Webkul Software Pvt Ltd.