 ShowAll Questionssorted byDate Posted Mano 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,
Manohar  Pratik (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 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 Zentgraf
Hi Manohar,

Regards
Robert
(mindforce: https://www.mind-force.de (https://www.mind-force.de))) Mano 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 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 sfdc
Hi Robert,

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 Sharma
Is there any issue in using the field update for this ? Robert 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 sfdc
Hi Bhawani/Robert,

Thanks,
Manohar Robert 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 sfdc
Hi Robert,

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