Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Tracy Oden 41Tracy Oden 41 

Help with a formula to display dates between two dates

Hi,

I need help with a formula that will display the dates between a start and end date. ie, if start date is June 28, 2019 and end date is July 2, 2019; then the dates in between are June 29, 2019, June 30, 2019 and July 1, 2019.
Best Answer chosen by Tracy Oden 41
ApuroopApuroop
Hello Tracy,

I'm almost sure that this requirement is not possible with a formula field. You need to use a trigger. 

Firstly you need to create a Long Text Area(32768) field on your object to save all these dates, set the visible lines to 10 or your wish. Make this field Read-Only from the page layout.

I have created a field with the API name: Available_Dates__c.

Create a trigger on your object. I created it on Opportunity like below.  
trigger getDates on Opportunity (before insert, before update) {
    Integer loopSize = 0;
    String str = '';
    List<Opportunity> oppList = new List<Opportunity>();
    for(Opportunity o : Trigger.new){
        if(o.Start_Date__c < o.CloseDate){
            System.debug('INSIDE THE LOOOP');
            loopSize = (o.Start_Date__c).daysBetween(o.CloseDate);
            for(Integer i = 2; i <= loopSize; i++){
                Date addingDAte = o.Start_Date__c.addDays(i);
                Integer year_p = addingDate.year();
                Integer month_p = addingDate.month();
                Integer day_p = addingDate.day();
                DateTime myDT = DateTime.newInstanceGmt(year_p, month_p, day_p);
                str = str+ myDT.format('MMMM d, yyyy') + '\n';
            }
            o.Available_Dates__c = str;
        }else{
            o.Available_Dates__c = 'Start date is in the future. Make sure close date is after the start date.';
            continue;
        }
    }
}
Result:

User-added image
Make the required changes and let me know if you need any assistance. :)

All Answers

ApuroopApuroop
Hello Tracy,

I'm almost sure that this requirement is not possible with a formula field. You need to use a trigger. 

Firstly you need to create a Long Text Area(32768) field on your object to save all these dates, set the visible lines to 10 or your wish. Make this field Read-Only from the page layout.

I have created a field with the API name: Available_Dates__c.

Create a trigger on your object. I created it on Opportunity like below.  
trigger getDates on Opportunity (before insert, before update) {
    Integer loopSize = 0;
    String str = '';
    List<Opportunity> oppList = new List<Opportunity>();
    for(Opportunity o : Trigger.new){
        if(o.Start_Date__c < o.CloseDate){
            System.debug('INSIDE THE LOOOP');
            loopSize = (o.Start_Date__c).daysBetween(o.CloseDate);
            for(Integer i = 2; i <= loopSize; i++){
                Date addingDAte = o.Start_Date__c.addDays(i);
                Integer year_p = addingDate.year();
                Integer month_p = addingDate.month();
                Integer day_p = addingDate.day();
                DateTime myDT = DateTime.newInstanceGmt(year_p, month_p, day_p);
                str = str+ myDT.format('MMMM d, yyyy') + '\n';
            }
            o.Available_Dates__c = str;
        }else{
            o.Available_Dates__c = 'Start date is in the future. Make sure close date is after the start date.';
            continue;
        }
    }
}
Result:

User-added image
Make the required changes and let me know if you need any assistance. :)
This was selected as the best answer
Alain CabonAlain Cabon
@Tracy Oden

You cannot use a loop with a formula (impossible) so the only limited solution is below with about 10 days max.

User-added image
  
IF (date2__c - date1__c > 5,"Too many date", 
IF (date1__c + 1 < date2__c, 
CASE(MONTH( date1__c + 1 ), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None") + ' ' + TEXT(DAY( date1__c + 1 )) + ', ' + TEXT(YEAR( date1__c + 1 )),"") 
+ 
IF (date1__c + 2 < date2__c, 
", " + CASE(MONTH( date1__c + 2 ), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None") + ' ' + TEXT(DAY( date1__c + 2 )) + ', ' + TEXT(YEAR( date1__c + 2 )),"") 
+ 
IF (date1__c + 3 < date2__c, 
", " + CASE(MONTH( date1__c + 3 ), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None") + ' ' + TEXT(DAY( date1__c + 3 )) + ', ' + TEXT(YEAR( date1__c + 3 )),"") 
+ 
IF (date1__c + 4 < date2__c, 
", " + CASE(MONTH( date1__c + 4 ), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None") + ' ' + TEXT(DAY( date1__c + 4 )) + ', ' + TEXT(YEAR( date1__c + 4 )),"") 
+ 
IF (date1__c + 5 < date2__c, 
", " + CASE(MONTH( date1__c + 5 ), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None") + ' ' + TEXT(DAY( date1__c + 5 )) + ', ' + TEXT(YEAR( date1__c + 5 )),"") 
)

You can add some days ( compiled size about 3,000 characters here for 5,000 max. )

You can create some sub-formulae ( first 10 days, next 10 days, etc ) if you can to cover a month ( about three formulae ) that you concatenate with a final formula.

Other problem : the "and" before the last day is a complication that must be replaced by a simple comma if you uise a huge formula.

 June 29, 2019, June 30, 2019 and July 1, 2019.
 June 29, 2019, June 30, 2019, July 1, 2019.
 
Alain CabonAlain Cabon

You can create some sub-formulae ( first 10 days, next 10 days, etc ) if you want to cover a month ( about three formulae ) that you concatenate with a final formula.

The concatenate formula would have a total compiled size far too big (exceeding the limit). That is impossible.

That is the main problem with formulae.