You need to sign in to do that
Don't have an account?
Christopher Milner
Calculate Case Days Open Excluding Weekends
I created a new custom case field called "Business Days Case Open" and pulled this equation from another question (posted back in 2014).
This works great if the case is open, but once we close a case, the field only returns a value of "0". Any insight on how I can get this to properly calculate even if the case is closed? It should be calculating the time between the Created Date and the current Today date. I have poked at this a little bit with no luck. Any help would be appreciated!
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)))
This works great if the case is open, but once we close a case, the field only returns a value of "0". Any insight on how I can get this to properly calculate even if the case is closed? It should be calculating the time between the Created Date and the current Today date. I have poked at this a little bit with no luck. Any help would be appreciated!
All Answers