Sign Up ›

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

Case formula return error

Hi, 

 I am adding number of years or months or days based on the pick list value selected below is the case formula used it is working proper for years values for months and days it is displaying error please suggest what might be the mistake in the below formula.  
CASE(1,
     IF(ISPICKVAL (cldy_uom__c, "Year(s)"),1,0), 
     (  DATE(YEAR(cldy_subscription_start_date__c) + VALUE(Subscription_Term_Formula__c),
             MONTH(cldy_subscription_start_date__c),
             DAY(cldy_subscription_start_date__c))
      ),
      IF(ISPICKVAL (cldy_uom__c, "Month(s)"),1,0), 
     (  
       DATE(YEAR(cldy_subscription_start_date__c),
            MONTH(cldy_subscription_start_date__c) + VALUE(Subscription_Term_Formula__c),
            DAY(cldy_subscription_start_date__c))
      ),
      IF(ISPICKVAL (cldy_uom__c, "Day(s)"),1,0), 
     (  
       DATE(YEAR(cldy_subscription_start_date__c),
            MONTH(cldy_subscription_start_date__c),
            DAY(cldy_subscription_start_date__c) + VALUE(Subscription_Term_Formula__c))
      ),
        ( DATE(YEAR(cldy_subscription_start_date__c),
            MONTH(cldy_subscription_start_date__c),
            DAY(cldy_subscription_start_date__c) ) )
)

Thanks
Sudhir
KarinGKarinG
Hi,

What is your Subscription_Term_Formula__c?
GMASJGMASJ
Hi Karin, 

   Sorry for the detail Subscription_Term_Formula__c was another formula value been used now have modified removing this field please find updated formula below please check and let me know I am still getting error sometime for days options
  
CASE(NULL,
     IF(ISPICKVAL (cldy_uom__c, "Year(s)"),1,0), 
     (  DATE(YEAR(cldy_subscription_start_date__c) + VALUE(TEXT(Subscription_Terms__c)),
             MONTH(cldy_subscription_start_date__c),
             DAY(cldy_subscription_start_date__c))
      ),
      IF(ISPICKVAL (cldy_uom__c, "Month(s)"),1,0), 
     (  
       DATE(YEAR(cldy_subscription_start_date__c),
            MONTH(cldy_subscription_start_date__c) + VALUE(TEXT(Subscription_Terms__c)),
            DAY(cldy_subscription_start_date__c))
      ),
      IF(ISPICKVAL (cldy_uom__c, "Day(s)"),1,0), 
     (  
       DATE(YEAR(cldy_subscription_start_date__c),
            MONTH(cldy_subscription_start_date__c),
            DAY(cldy_subscription_start_date__c) + VALUE(TEXT(Subscription_Terms__c)) )
      ),
        ( DATE(YEAR(cldy_subscription_start_date__c),
            MONTH(cldy_subscription_start_date__c),
            DAY(cldy_subscription_start_date__c) ) )
)

Thanks
Sudhir
KarinGKarinG
Hi Sudhir,

Use this 
CASE( TEXT(cldy_uom__c) , 
'Year(s)', 
 DATE((YEAR( cldy_subscription_start_date__c )+VALUE(Subscription_Term_Formula__c)),
            MONTH( cldy_subscription_start_date__c ),
            DAY( cldy_subscription_start_date__c ) )  , 
'Month(s)', 
DATE(YEAR( cldy_subscription_start_date__c ),
            (MONTH( cldy_subscription_start_date__c )+VALUE(Subscription_Term_Formula__c)),
            DAY( cldy_subscription_start_date__c ) ) ,
'Day(s)',
DATE(YEAR( cldy_subscription_start_date__c ),
            MONTH( cldy_subscription_start_date__c ),
            (DAY( cldy_subscription_start_date__c )+VALUE(Subscription_Term_Formula__c)) ) ,
 cldy_subscription_start_date__c
)

If this does not work, the problem lies with Subscription_Term_Formula__c.
VineetKumarVineetKumar
What's the error?