I have one custom formula field "Case Age" which displays the Case Age in the format of "xx days yy hours zz mins" which is using the below formula,

TEXT(FLOOR((Case_Custom_Age__c)/(24*60))) + 'd ' + TEXT(FLOOR(MOD(Case_Custom_Age__c,(24*60))/60)) + 'hrs ' + TEXT(FLOOR(MOD(MOD((Case_Custom_Age__c), (24*60)),60))) + 'mins'

But the issue here is, in the List View, it's not sorting properly since it's text datatype and the characters are also appended (actually sorting occurs as per text sorting functionality). I need to sort based on numeric. It should consider all the numeric values. Please help to fix this.

  • Why dont you sort by case_custom_age__c. It should sort properly. You dont want to put it on the list view? – erkan çipil May 19 '17 at 7:51
  • Hi @erkançipil, We want to format the case age like "xx days yy hours zz mins". I have tried the ways of formatting the formula, but sorting won't come in desired way since the formala data type is "Text". if we change to 'Number', appending the chars is not possible. – sridhar May 19 '17 at 8:54

You can sort your field, but you need to add in extra zeroes on the left side to make sure that, for example, 2 sorts before 19; using LPAD, you end up with 02 and 19, so it will sort lexicographically.

LPAD(TEXT(FLOOR(        Case_Custom_Age__c/1440     )),4,'0') + 'd ' + 
LPAD(TEXT(FLOOR(MOD(    Case_Custom_Age__c,1440)/60 )),2,'0') + 'h ' + 
LPAD(TEXT(FLOOR(MOD(MOD(Case_Custom_Age__c,1440),60))),2,'0') + 'm'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.