ShowAll Questionssorted byDate Posted
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).
```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!
Best Answer chosen by Christopher Milner
Christopher Milner
This ended up working:
```IF(
IsClosed,
(5 * ( FLOOR( ( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) ),
(5 * ( FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( TODAY() - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) )
)```

Raj Vakati
Remove Isclosed from the formual .. and try this once

```(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)```

Christopher Milner
Raj, thanks for the response! However, this just gives me syntax error after syntax error. The Salesforce recommended edits only seem to make it worse.
Raj Vakati
Use this

```(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))```

Christopher Milner
Once again, appreciate you chiming in! The latest iteration is returning a value of "0" for closed cases and nothing at all for open cases.
Christopher Milner
This ended up working:
```IF(
IsClosed,
(5 * ( FLOOR( ( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) ),
(5 * ( FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( TODAY() - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) )
)```
This was selected as the best answer