Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
nkbk0108

Hi.

We are trying to get a case age formual to work like one that we currently have in an excel spreadsheet.

Custom Date Fields:
Date Opened - 1/3/2012 9:43am
Date Resolved - 1/18/2012 8:42am

Excel Formula: =IF(C5654="",0,(NETWORKDAYS(B5654,C5654,holidays)-1)*(end-start)+IF(NETWORKDAYS(C5654,C5654,holidays),MEDIAN(MOD(C5654,1),
end,start),end)-MEDIAN(NETWORKDAYS(B5654,B5654,holidays)*MOD(B5654,1),end,start))

EXCEL Age in Business Hours: 120:17

Salesforce Formula:
If(IsNull(Date_Resolved__c ),

CASE(MOD( DateValue(Date_Opened__c ) - DATE(1985,6,24),7), 0 ,
CASE( MOD( Today() - DateValue(Date_Opened__c ) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 ,
CASE( MOD( Today() - DateValue(Date_Opened__c ) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 ,
CASE( MOD( Today() - DateValue(Date_Opened__c ) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 ,
CASE( MOD( Today() - DateValue(Date_Opened__c ) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 ,
CASE( MOD( Today() - DateValue(Date_Opened__c ) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 ,
CASE( MOD( Today() - DateValue(Date_Opened__c ) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 ,
CASE( MOD( Today() - DateValue(Date_Opened__c ) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999)
+ (FLOOR(( Today() - DateValue(Date_Opened__c ) )/7)*5-1)*11,

CASE(MOD( DateValue(Date_Opened__c ) - DATE(1985,6,24),7), 0 ,
CASE( MOD( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 ,
CASE( MOD( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 ,
CASE( MOD( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 ,
CASE( MOD( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 ,
CASE( MOD( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 ,
CASE( MOD( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 ,
CASE( MOD( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999)
+ (FLOOR(( DateValue(Date_Resolved__c ) - DateValue(Date_Opened__c ) )/7)*5-1))*11

SALESFORCE Age in Business Hours: 121.00

How can I get the SFDC formula to stop rounding? I think that's what it's doing.

The Case Age App doesn't work for what we need. We need the age before, during and after close.

THANK YOU SO MUCH!
Nancy

KierenJameson
Possible silly question, but have you set the formula field to have two decimal places?