function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

Reducing the size of a Formula field creating the duratin of an event

Hello everyone,
I jusr read the document 'Tips for Reducing Formula Size'; Still I can't find a way to reduce the size of my custom formula fied. Any idea will be helpful. The goal is to present to the user the 'Duration' of an event based on the DateStart and DateFinish, in the shortest possible way, meaning:
Mon. May 18, 2015, Fri. Jun. 17, 2016 -  Year is different
Mon. May 18, Wed. Jun. 17, 2015 – same year, but monthis  different
Mon. 18-Wed. 20, May 2015 – same year and month, but day is different
Mon. May 18, 2015 – same day
Below my current formula that exceed the Compiled 4,000 bytes allowance. below DS__c is DateStart__c, DF__c is DateFinish, D1-7 is day in the week (Mon-Sun), M1-12 is month in the year (Jan-Dec)
Again, thanks for any idea (but doing a Workflow which I would like to avoid).

If (DS__c = DF__c,  /* Mon. Apr. 17, 2015 */
CASE( MOD( DF__c - DATE( 1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )
+'. '+
CASE (MONTH(DF__c), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)
+'. '+
TEXT(DAY(DF__c))
+', '+
TEXT(YEAR(DF__c))
,
IF (YEAR(DS__c) == YEAR(DF__c),     /* Same year: Mon. 17-Tue. 18, Apr. 2015,  */
IF (MONTH(DS__c) == MONTH(DF__c), /* same year, same month: Mon. 17-Tue. 18, Apr. 2015 */
CASE( MOD(DS__c - DATE(1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )
+'. '+
TEXT(DAY(DS__c))
+'-'+
CASE( MOD( DF__c - DATE( 1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )
+'. '+
TEXT(DAY(DF__c))
+', '+
CASE (MONTH( DS__c), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)
+'. '+
TEXT(YEAR(DF__c))
,                                                  /* Same year, different months: Mon. Apr. 17-Wed. May 20, 2015 */
CASE( MOD(DS__c - DATE( 1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )
+'. '+
CASE (MONTH( DS__c), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)
+'. '+
TEXT(DAY(DS__c))
+'-'+
CASE( MOD( DF__c - DATE( 1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )
+'. '+
CASE (MONTH( DF__c ), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)
+'. '+
TEXT(DAY(DF__c))
+', '+
TEXT(YEAR(DF__c))
)
,                                                     /* Different year, month irrelevant: Mon. Apr. 17, 2015-Wed. May 20, 2016 */
CASE( MOD(DS__c - DATE( 1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )
+'. '+
CASE (MONTH( DS__c), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)
+'. '+
TEXT(DAY(DS__c))
+', '+
TEXT(YEAR(DS__c))
+'-'+
CASE( MOD( DF__c - DATE( 1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )
+'. '+
CASE (MONTH( DF__c ), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)
+'. '+
TEXT(DAY(DF__c))
+', '+
TEXT(YEAR(DF__c))
)
)
 
Best Answer chosen by FastSnail
Sanjay Mulagada 11Sanjay Mulagada 11
   I notice "CASE( MOD(DS__c - DATE(1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )" being repwatedly used whenever the situation "Same year"
so I would first try to break that case Start a IF clause which checks for If (DS__c = DF__c, CASE( MOD(DS__c - DATE(1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 ) + now check for different scenarios again as in different month and different day breakdown.
   If its still break then try the same process^ to "CASE (MONTH( DS__c), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)" to avoid being re-used.
   Let me know for any questions.
 

All Answers

Sanjay Mulagada 11Sanjay Mulagada 11
   I notice "CASE( MOD(DS__c - DATE(1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 )" being repwatedly used whenever the situation "Same year"
so I would first try to break that case Start a IF clause which checks for If (DS__c = DF__c, CASE( MOD(DS__c - DATE(1900, 1, 7 ), 7 ), 0, $Label.D7, 1, $Label.D1, 2, $Label.D2, 3, $Label.D3, 4, $Label.D4, 5, $Label.D5, $Label.D6 ) + now check for different scenarios again as in different month and different day breakdown.
   If its still break then try the same process^ to "CASE (MONTH( DS__c), 1, $Label.M1, 2, $Label.M2, 3, $Label.M3, 4, $Label.M4, 5, $Label.M5, 6, $Label.M6, 7, $Label.M7, 8, $Label.M8, 9, $Label.M9, 10, $Label.M10, 11, $Label.M11, $Label.M12)" to avoid being re-used.
   Let me know for any questions.
 
This was selected as the best answer
FastSnailFastSnail
A big THANK YOU Sanjay,
Below the result after 3 hours of hard thinking. Much smaller and efficient. Thanks again.
/*P1 1234*/CASE(MOD(DateFinish__c-DATE( 1900, 1,7),7 )
,0,$Label.D7,1,$Label.D1,2,$Label.D2,3,$Label.D3,4,$Label.D4,5,$Label.D5,$Label.D6)
+'. '
+If(YEAR(DateStart__c)==YEAR(DateFinish__c) && MONTH(DateStart__c)==MONTH(DateFinish__c) && DateStart__c!=DateFinish__c /*Case 3*/
/*P3 3*/, TEXT(DAY(DateStart__c))
+'-'
/*P5 3*/+CASE(MOD(DateFinish__c-DATE(1900,1,7),7),0,$Label.D7,1,$Label.D1,2,$Label.D2,3,$Label.D3,4,$Label.D4,5,$Label.D5,$Label.D6)
+'. '+
/*P7 3*/TEXT(DAY(DateFinish__c))
+'. '
/*P8 3*/+CASE(MONTH(DateFinish__c),1,$Label.M1,2,$Label.M2,3,$Label.M3,4,$Label.M4,5,$Label.M5,6,$Label.M6,7,$Label.M7,8,$Label.M8,9,$Label.M9,10,$Label.M10,11,$Label.M11,$Label.M12)
/* below cases 1,2,4 */
/*P2 124*/, CASE(MONTH(DateStart__c),1,$Label.M1,2,$Label.M2,3,$Label.M3,4,$Label.M4,5,$Label.M5,6,$Label.M6,7,$Label.M7,8,$Label.M8,9,$Label.M9,10,$Label.M10,11,$Label.M11,$Label.M12)
+' '
/*P3 124*/+TEXT(DAY(DateStart__c))
+If(DateStart__c != DateFinish__c  /*Case 12*/
,
If(YEAR(DateStart__c)!=YEAR(DateFinish__c)  /*Case 1*/
/*P4 1*/, ' '+TEXT(YEAR(DateStart__c))+'-', '')
/*P5 12*/+CASE(MOD(DateFinish__c-DATE(1900,1,7),7),0,$Label.D7,1,$Label.D1,2,$Label.D2,3,$Label.D3,4,$Label.D4,5,$Label.D5,$Label.D6)
+'. '
/*P6 12*/+CASE(MONTH(DateFinish__c),1,$Label.M1,2,$Label.M2,3,$Label.M3,4,$Label.M4,5,$Label.M5,6,$Label.M6,7,$Label.M7,8,$Label.M8,9,$Label.M9,10,$Label.M10,11,$Label.M11,$Label.M12)
+' '
/*P7 12*/+TEXT(DAY(DateFinish__c))
, ''
)
)
/*P9 1234 */+' '+TEXT(YEAR(DateFinish__c))

 
Sanjay Mulagada 11Sanjay Mulagada 11
Awsome ! glad I could help :)