Skip to main content
Dawn Fisher (Black Duck) 님이 #Formulas에 질문했습니다

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)

)

답변 1개
  1. 2024년 8월 29일 오후 7:13

    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

    )

0/9000