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