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
Suma Ganga

# Case Age Formula

Hi,

i have a requirement like, calculating CASE Age.
I have 2 differrent objects with the same formula , Risk Age and Decision Age.
i am using below formula::

IF( ISBLANK( Actual_Close_Date__c ) ,
IF( ISBLANK( Raised_On__c ), '0',
IF( Raised_On__c <= TODAY() ,
TEXT(CASE(MOD( Raised_On__c - DATE(1985,6,24),7),

0 , CASE( MOD( TODAY()-Raised_On__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( TODAY()-Raised_On__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( TODAY()-Raised_On__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( TODAY()-Raised_On__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( TODAY()-Raised_On__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( TODAY()-Raised_On__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( TODAY()-Raised_On__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)

(FLOOR(( TODAY()-Raised_On__c)/7)*5)-1
),
IF(CASE(MOD( TODAY() - DATE(1985,6,24),7),

0 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( Raised_On__c - TODAY() ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( Raised_On__c - TODAY() ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( Raised_On__c - TODAY() ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)

(FLOOR(( Raised_On__c - TODAY())/7)*5)-1 == 0, '0', '-' + TEXT(CASE(MOD( TODAY() - DATE(1985,6,24),7),

0 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( Raised_On__c - TODAY() ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( Raised_On__c - TODAY() ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( Raised_On__c - TODAY() ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( Raised_On__c - TODAY() ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)

(FLOOR(( Raised_On__c - TODAY())/7)*5)-1

,'Closed')

Here, Raised_On__c  field is formula for one object and user can enter the date in one object.

The formula is working fine with the second object but not first.
Raised_On__c  is a forlula formula field in first object....
formula is like this :: createddate. this will return date value only...

but, Risk Age is giving wrong values...

any help is appriciated...