Sunday, March 2, 2014

Case Age Calculation by including Business Hours

Case Age Calculation by including Business Hours:
The following formula calculates case age for the business hours CST Mon-Fri 8 am-6pm
between the opened date/time and closed date/time

IF (  IsClosed ,

ROUND(
10*(
(5*FLOOR((DATEVALUE (   ClosedDate  ) -DATE(1900,1,8))/7) +
MIN(5,
MOD(DATEVALUE (ClosedDate  ) -DATE(1900,1,8), 7)) +
IF(
CASE(
MOD(
datevalue(ClosedDate  )  - DATE(1985, 1, 5), 7
)
, 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0

<>  0,
MIN(1, 24/10*(if(ClosedDate  >DATETIMEVALUE(TEXT(Datevalue(ClosedDate  )) & ' 14:00:00'),MOD( ClosedDate  -DATETIMEVALUE('1900-01-08 14:00:00'), 1),0))),
0
)

)
-
(5*FLOOR((DATEVALUE( CreatedDate )-DATE(1900,1,8))/7) +
MIN(5,
MOD(DATEVALUE(CreatedDate )-DATE(1900,01,08), 7)) +
IF(
CASE(
MOD(
datevalue(CreatedDate )  - DATE(1985, 1, 5), 7
)
, 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0

<>  0,
MIN(1, 24/10*(if(CreatedDate >DATETIMEVALUE(TEXT(Datevalue(CreatedDate )) & ' 14:00:00'),MOD( CreatedDate -DATETIMEVALUE('1900-01-08 14:00:00'), 1),0))),
0
)
)
), 0)

,
ROUND(
10*(
(5*FLOOR((DATEVALUE (  NOW() ) -DATE(1900,1,8))/7) +
MIN(5,
MOD(DATEVALUE (NOW()) -DATE(1900,1,8), 7)) +
IF(
CASE(
MOD(
datevalue(NOW())  - DATE(1985, 1, 5), 7
)
, 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0

<>  0,
MIN(1, 24/10*(if(NOW()>DATETIMEVALUE(TEXT(Datevalue(NOW())) & ' 14:00:00'),MOD( NOW()-DATETIMEVALUE('1900-01-08 14:00:00'), 1),0))),
0
)

)
-
(5*FLOOR((DATEVALUE( CreatedDate )-DATE(1900,1,8))/7) +
MIN(5,
MOD(DATEVALUE(CreatedDate )-DATE(1900,01,08), 7)) +
IF(
CASE(
MOD(
datevalue(CreatedDate )  - DATE(1985, 1, 5), 7
)
, 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0

<>  0,
MIN(1, 24/10*(if(CreatedDate >DATETIMEVALUE(TEXT(Datevalue(CreatedDate )) & ' 14:00:00'),MOD( CreatedDate -DATETIMEVALUE('1900-01-08 14:00:00'), 1),0))),
0
)
)
), 0)
)

 

No comments:

Post a Comment