Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mano sfdcMano sfdc 

Formula help - Compress my formula characters from 5,153 to below 5,000 characters

Hi Experts,

I need a Formula help.
I have two date fields(T__c, F__c), I need difference between dates format like X Year(s) X Month(s) X Day(s)

To Date field: T__c
From Date field:  F__c

My formula is below..

IF(AND(Year(T__c)=Year(F__c),Month(T__c)>=Month(F__c),Day(T__c)>=Day(F__c)),TEXT(Month(T__c)-Month(F__c))&" Month(s) "&""&TEXT(Day(T__c)-Day(F__c))&" Day(s) ",
IF(AND(Year(T__c)=Year(F__c),Month(T__c)>Month(F__c),Day(T__c)<Day(F__c)),TEXT((Month(T__c)-Month(F__c))-1)&" Month(s) "&""&TEXT((30+Day(T__c))-Day(F__c))&" Day(s) ",

IF(AND(Year(T__c)>Year(F__c),Month(T__c)=Month(F__c),Day(T__c)=Day(F__c)),TEXT(Year(T__c)-Year(F__c))&" Year(s) ",

IF(AND(Year(T__c)>Year(F__c),Month(T__c)>Month(F__c),Day(T__c)=Day(F__c)),TEXT(Year(T__c)-Year(F__c))&" Year(s) "&""&TEXT(Month(T__c)-Month(F__c))&" Month(s) ",
IF(AND(Year(T__c)>Year(F__c),Month(T__c)>Month(F__c),Day(T__c)>Day(F__c)),TEXT(Year(T__c)-Year(F__c))&" Year(s) "&""&TEXT(Month(T__c)-Month(F__c))&" Month(s) "&""&TEXT(Day(T__c)-Day(F__c))&" Day(s) ",
IF(AND(Year(T__c)>Year(F__c),Month(T__c)>Month(F__c),Day(T__c)<Day(F__c)),TEXT(Year(T__c)-Year(F__c))&" Year(s) "&""&TEXT((Month(T__c)-Month(F__c))-1)&" Month(s) "&""&TEXT((30+Day(T__c))-Day(F__c))&" Day(s) ",

IF(AND(Year(T__c)>Year(F__c),Month(T__c)<Month(F__c),Day(T__c)=Day(F__c)),TEXT((Year(T__c)-Year(F__c))-1)&" Year(s) "&""&TEXT((30+Month(T__c))-Month(F__c))&" Month(s) ",
IF(AND(Year(T__c)>Year(F__c),Month(T__c)<Month(F__c),Day(T__c)>Day(F__c)),TEXT((Year(T__c)-Year(F__c))-1)&" Year(s) "&""&TEXT((12+Month(T__c))-Month(F__c))&" Month(s) "&""&TEXT(Day(T__c)-Day(F__c))&" Day(s) ",
IF(AND(Year(T__c)>Year(F__c),Month(T__c)<Month(F__c),Day(T__c)<Day(F__c)),TEXT((Year(T__c)-Year(F__c))-1)&" Year(s) "&""&TEXT(((12+Month(T__c))-Month(F__c))-1)&" Month(s) "&""&TEXT((30+Day(T__c))-Day(F__c))&" Day(s) ", 
TEXT(0) 
)))))))))

Formula is perfect, but it showing below error.

Error:  Compiled formula is too big to execute (5,153 characters). Maximum size is 5,000 characters.

Anyone can please sort out this.
Thanks in advance.

Thanks,
Manohar
PratikPratik (Salesforce Developers) 
Hi Manohar,

Here is the Salesforce guide to reduce the size of formula:
https://help.salesforce.com/help/pdfs/en/salesforce_formula_size_tipsheet.pdf

Thanks,
Pratik
Bhawani SharmaBhawani Sharma
Can you try this using a field update? What all you need to do is, create a workflow rule which with update a field  value based on your formula.
Robert ZentgrafRobert Zentgraf
Hi Manohar,

I'm not sure about your formula. But you can create an additional field, where you can add a part of your formula.

Regards
Robert
(mindforce: https://www.mind-force.de (https://www.mind-force.de)))
Mano sfdcMano sfdc
Hi All,

Note: for this formula I need to calculate values like below.

Year = 12 Months (12*30=360 Days)
Month = 30 Days (Forget about 28/29/30/31)
 
But formula output should like below.
X Year(s) X Month(s) X Day(s)
Ex:  2 Year(s) 6 Month(s) 24 Day(s)
 
Thanks,
Manohar
Robert ZentgrafRobert Zentgraf
Hi Manohar,

you can use a formular like this:
TEXT(T__c - F__c) => this will show you the difference in days. So, then you are able to calculate the required infos.

Regards
Robert
(Mindforce: http://www.mind-force.de)
Mano sfdcMano sfdc
Hi Robert,

Thanks for the reply.
But it gives output value according to month (28/30/31), But i need every month value as 30 Days.
my formula is working fine, please reduce the characters up to 5,000 Thats it.
Bhawani SharmaBhawani Sharma
Is there any issue in using the field update for this ?
Robert ZentgrafRobert Zentgraf
Hi Manohar,

perhaps you can change this issue in this way:
IF(Year(T__c)=Year(F__c), "0 Year(s)", TEXT((Year(T__c)-Year(F__c))-1) & " Year(s)") &
IF(Month(T__c)=Month(F__c), ..., ...) &
IF(Month(T__c)>Month(F__c), ..., ...) &
IF(Month(T__c)<Month(F__c), ..., ...) &
...
and so on.

Regards
Robert
(mindforce: http://www.mind-force.de)
Mano sfdcMano sfdc
Hi Bhawani/Robert,

Thanks for the reply.
Robert can you please elaborate your formula.

Thanks,
Manohar
Robert ZentgrafRobert Zentgraf
Hi Manohar,

this should be working (not tested):
IF(Year(T__c)=Year(F__c), "0 Year(s)", TEXT((Year(T__c)-Year(F__c))-1) & " Year(s)") &
IF(Month(T__c)=Month(F__c), "0 Month(s)") &
IF(Month(T__c)>Month(F__c), TEXT(Month(T__c)-Month(F__c))&" Month(s) ") &
IF(Month(T__c)<Month(F__c), TEXT((12+Month(T__c))-Month(F__c))&" Month(s) ") &
IF(Day(T__c)=Day(F__c), " 0 Day(s)") &
IF(Day(T__c)>Day(F__c), TEXT(Day(T__c)-Day(F__c))&" Day(s) ") &
IF(Day(T__c)<Day(F__c), TEXT((30+Day(T__c))-Day(F__c))&" Day(s) ")

Regards
Robert
(mindforce: http://www.mind-force.de)
Mano sfdcMano sfdc
Hi Robert,

It's not working.It showing errors...
Robert ZentgrafRobert Zentgraf
Which errors?