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
kamal3883kamal3883 

Case age calculation

Hi, I want to implement case age using formula field. my requirement is like this. 1) Only weekdays are business days. 2) i also want to use business hours. suppose if business hour is 8am to 8 pm. so after 10 days it should say 120 not 240 hours.

 

i tried this formula 

1) CASE(MOD( datevalue(CreatedDate)- DATE(1985,6,24),7), 

0 , CASE( MOD( ClosedDate - CreatedDate,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( ClosedDate - CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( ClosedDate - CreatedDate,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( ClosedDate - CreatedDate,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( ClosedDate - CreatedDate,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( ClosedDate - CreatedDate,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( ClosedDate - CreatedDate,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( ClosedDate - CreatedDate)/7)*5)

 

 

Ashish_SFDCAshish_SFDC
Hi Kamal, 

This would work for days IF(IsClosed = True,

(DATEVALUE(ClosedDate) - DATEVALUE( CreatedDate )) -
( FLOOR ( ( ( (DATEVALUE(ClosedDate)) - DATEVALUE(CreatedDate)) / 7 ) ) * 2 ) +
CASE(MOD ( DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7),
0,CASE( MOD ((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7),0,1,2 ),
1,CASE( MOD ((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7),0,2,1 ),
IF(MOD(DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7) - MOD((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7) <= 0 ,0,
IF(MOD((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7) = 0,1,-2 ) ) )
,
((TODAY()) - DATEVALUE( CreatedDate )) -
( FLOOR ( ( ( (TODAY()) - DATEVALUE(CreatedDate)) / 7 ) ) * 2 ) +
CASE(MOD ( DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7),
0,CASE( MOD ((TODAY()) - DATE( 1900,1,6 ),7),0,1,2 ),
1,CASE( MOD ((TODAY()) - DATE( 1900,1,6 ),7),0,2,1 ),
IF(MOD(DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7) - MOD((TODAY()) - DATE( 1900,1,6 ),7) <= 0 ,0,
IF(MOD((TODAY()) - DATE( 1900,1,6 ),7) = 0,1,-2 ) ) )
)

This assumes M-F business week, what kind of business hours are you working with? Is it just a simple 8-5 M-F schedule?

https://success.salesforce.com/answers?id=90630000000gkUWAAY

Regards,
Ashish