Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SolutionSolution 

Calculate Case Age excluding weekends

Hi,

I need to create one formula field (returns number) which will calculate Case Age (In days) such that it will keep incrementing until case is closed. If the case is closed it will stop counting. This excludes weekends.

Can someone please help me on this?

Thanks,
Pooja
MagulanDuraipandianMagulanDuraipandian
https://appexchange.salesforce.com/listingDetail?listingId=a0N300000016ccNEAQ&tab=r&revId=a0S300000018nVLEAY..

https://success.salesforce.com/questionDetail?qid=a1X30000000c6h7EAA

If this solves your problem, kindly mark it as the best answer.

Regards,
Magulan
http://www.infallibletechie.com
SolutionSolution
Thanks for the response. I checked the detail for AppExchange product, it enables reporting on the time that a case has spent with support, the time it's spent awaiting the customer, and its total age in business hour. But I want to display Case Age field on the standard page layout which I am not sure can be achieved by this app.


I checked the formula also on the given link. Tried same formula in my org but received an error while saving it. The error is "Compiled formula is too big to execute (5,227 characters). Maximum size is 5,000 characters (Related field: Formula)"
Chandra PrakashChandra Prakash
Hi,

Try This  Formula Take  Custom Fields & Relationships > Formula > Select number. then write below Formula..

DAY( End_Date__c)+ DAY(Start_Date__c)-CASE(MOD( Start_Date__c - DATE(1985,6,24),7),
  0 , CASE( MOD( End_Date__c - Start_Date__c , 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
  1 , CASE( MOD( End_Date__c - Start_Date__c , 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
  2 , CASE( MOD( End_Date__c - Start_Date__c , 7),0,0,1,0,2,0,3,1,2),
  3 , CASE( MOD( End_Date__c - Start_Date__c , 7),0,0,1,0,2,1,2),
  4 , CASE( MOD( End_Date__c - Start_Date__c , 7),0,0,1,1,2),
  5 , CASE( MOD( End_Date__c - Start_Date__c , 7),0,1,2),
  6 , CASE( MOD( End_Date__c - Start_Date__c , 7),6,2,1),
  999)
  +
  (FLOOR(( End_Date__c - Start_Date__c )/7)*2)


Regards,
Chandra Prakash Sharma
Bisp Solutions Inc.
http://bisptrainings.com
SolutionSolution
Hi Prakash,


I am sory but this formula is not working as expected.
Chandra PrakashChandra Prakash
Hi Solution,

Please share with me what is you actual recruitment For " Calculate Case Age excluding weekends".
AgiAgi
Hi,

If the case is closed, it is calculating no of business days between Created and Closed date, and when the case is open, it is showing business days between Today - Created date.

If( Isclosed,

(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)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)),

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((Today() - Datevalue(CreatedDate))/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)))
lkplkp
Hi Agi,

How did you get the number of days the case was open to show after the case was closed?

Thanks
Christopher MilnerChristopher Milner
Agi / Ikp:

Did you guys ever find a solution to this? I implemented the formula from Agi's suggestion in a formula field called "Business Days Case Open". Seems to work for open cases, but once closed I am only getting a value of 0.
Nicole Young 2Nicole Young 2
Hey Christopher, Did you ever find a solution for your issue? I'm currently having the same one. 
Mike GrindleMike Grindle
Good Afternoon, we had the same issue and fixed it by setting the first block of lines to also be date values. 

If( IsClosed,

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((Datevalue(ClosedDate) - Datevalue(CreatedDate))/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)),

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((Today() - Datevalue(CreatedDate))/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)))