Hi all,
I have a Case Age in Days that is already set up to exclude weekends. I'm trying to append it to also remove Durations of 2 different statuses. I have a Flow set up to populate the date fields and then I have a Duration field that calculates the status time. How would I append the below formula to minus the two duration fields?
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-1)
,
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-1)
)
Hi @Dawn Fisher,
Try this -
5 * (FLOOR((TODAY() - DATEVALUE(CreatedDate)) / 7)) +
MIN(5, MOD(TODAY() - DATEVALUE(CreatedDate), 7)) -
CASE(
/* Case when Created on a Weekend */
MOD(DATEVALUE(CreatedDate) - DATE(1985,6,24), 7),
0, IF(MOD(TODAY() - DATE(1985,6,24), 7) = 0, 1, 0),
6, IF(MOD(TODAY() - DATE(1985,6,24), 7) = 6, 1, 0),
0
)