You need to sign in to do that
Don't have an account?

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
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
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
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)))
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
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)
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.
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)
Thanks for the reply.
Robert can you please elaborate your formula.
Thanks,
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)
It's not working.It showing errors...