ShowAll Questionssorted byDate Posted
Solution

# 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.

Thanks,
Pooja
MagulanDuraipandian
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
Solution
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 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
Solution
Hi Prakash,

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

Please share with me what is you actual recruitment For " Calculate Case Age excluding weekends".
Agi
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)))
lkp
Hi Agi,

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

Thanks

Christopher 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 2
Hey Christopher, Did you ever find a solution for your issue? I'm currently having the same one.