function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Alequi10Alequi10 

Salesforce rounding formulas

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. 

Best Answer chosen by Admin (Salesforce Developers) 
Ron ReedRon Reed

You're converting the Date Time Values to just Dates by using the DateValue function. That would remove any references to time and cause it to calculate just using days.

All Answers

Ron ReedRon Reed

You're converting the Date Time Values to just Dates by using the DateValue function. That would remove any references to time and cause it to calculate just using days.

This was selected as the best answer
Alequi10Alequi10

Hi Ron,

 

Is there a way to use a similar formula but to calculate the time using hours and not just days?

 

Thanks in advance for your response.