Formula to pull out nearest date from multiple date fields - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Katie SilbigerKatie Silbiger 

Formula to pull out nearest date from multiple date fields

We have five date fields that represent deadlines.  We'd like to create a custom field that shows which of those dates is the next approaching deadline.  Some of those 5 date fields may be blank or in the past.  Any ideas on how to do this?
Best Answer chosen by Katie Silbiger
Katie SilbigerKatie Silbiger
I made some small changes based on the above formula and I think it's working!  Thanks so much for your help--I'm new to formulas so this is all witchcraft to me.

IF(
    AND(
        ISBLANK(Deadline_1__c),
        ISBLANK(Deadline_2__c),
        ISBLANK(Deadline_3__c),
        ISBLANK(Deadline_4__c),
        ISBLANK(Deadline_5__c)
    ),
    NULL,
    DATE(2100, 01, 01) +
    MIN(
        IF(
            ISBLANK(Deadline_1__c) || Deadline_1__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_1__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_2__c) || Deadline_2__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_2__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_3__c) || Deadline_3__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_3__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_4__c) || Deadline_4__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_4__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_5__c) || Deadline_5__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_5__c
        ) - DATE(2100, 01, 01)
    )
)

All Answers

Katie SilbigerKatie Silbiger
More info:
I was using this formula to see # of days until the nearest deadline, but it does not work if any of the 5 fields are blank.  It also does not exclude dates in the past.

MIN((Deadline_1__c-TODAY()),(Deadline_2__c-TODAY()),(Deadline_3__c-TODAY()),(Deadline_4__c-TODAY()),(Deadline_5__c-TODAY()))
Jeff MayJeff May
How about the MAX() function:

MAX(date1,
         MAX(date2,
                   MAX(date3, MAX(date4, date5))
          )
)
Deepak AnandDeepak Anand
How about this:
IF(
    AND(
        ISBLANK(Deadline_1__c),
        ISBLANK(Deadline_2__c),
        ISBLANK(Deadline_3__c),
        ISBLANK(Deadline_4__c),
        ISBLANK(Deadline_5__c)
    ),
    NULL,
    DATE(1900, 01, 01) + 
    MAX(
        BLANKVALUE(Deadline_1__c, DATE(1900, 01, 01)) - DATE(1900, 01, 01),
        BLANKVALUE(Deadline_2__c, DATE(1900, 01, 01)) - DATE(1900, 01, 01),
        BLANKVALUE(Deadline_3__c, DATE(1900, 01, 01)) - DATE(1900, 01, 01),
        BLANKVALUE(Deadline_4__c, DATE(1900, 01, 01)) - DATE(1900, 01, 01),
        BLANKVALUE(Deadline_5__c, DATE(1900, 01, 01)) - DATE(1900, 01, 01)
    )
)
Katie SilbigerKatie Silbiger
Deepak,

This works perfectly to find the highest date in the 5, but I am trying to find the date that is 1) not in the past, and 2) closest to today. For example, say today is 7/1/2014 and the dates I have are:

6/1/2014
6/5/2014
7/5/2014
blank
8/5/2014

I would want this to pull 7/5/2014.
Deepak AnandDeepak Anand
Alright. So I haven't tried it but worth a shot:
IF(
    AND(
        ISBLANK(Deadline_1__c),
        ISBLANK(Deadline_2__c),
        ISBLANK(Deadline_3__c),
        ISBLANK(Deadline_4__c),
        ISBLANK(Deadline_5__c)
    ),
    NULL,
    DATE(1900, 01, 01) + 
    MAX(
        IF(
            ISBLANK(Deadline_1__c) || Deadline_1__c > TODAY(), 
            DATE(1900, 01, 01), 
            Deadline_1__c
        ) - DATE(1900, 01, 01),
        IF(
            ISBLANK(Deadline_2__c) || Deadline_2__c > TODAY(), 
            DATE(1900, 01, 01), 
            Deadline_2__c
        ) - DATE(1900, 01, 01),
        IF(
            ISBLANK(Deadline_3__c) || Deadline_3__c > TODAY(), 
            DATE(1900, 01, 01), 
            Deadline_3__c
        ) - DATE(1900, 01, 01),
        IF(
            ISBLANK(Deadline_4__c) || Deadline_4__c > TODAY(), 
            DATE(1900, 01, 01), 
            Deadline_4__c
        ) - DATE(1900, 01, 01),
        IF(
            ISBLANK(Deadline_5__c) || Deadline_5__c > TODAY(), 
            DATE(1900, 01, 01), 
            Deadline_5__c
        ) - DATE(1900, 01, 01)
    )
)
Katie SilbigerKatie Silbiger
Hmm, in the above example, this is returning 6/5/2014.  Any ideas?
Deepak AnandDeepak Anand
So since 5/7/2014[DD/MM/YYYY] and 5/8/2014[DD/MM/YYYY] are greater than TODAY(), they were taken out(as per the above formula) thus leaving bhind: 1/6/2014 and 5/6/2014 and that gave: 5/6/2014

I don't know if this would even be possible but lemme think.....
Katie SilbigerKatie Silbiger
I made some small changes based on the above formula and I think it's working!  Thanks so much for your help--I'm new to formulas so this is all witchcraft to me.

IF(
    AND(
        ISBLANK(Deadline_1__c),
        ISBLANK(Deadline_2__c),
        ISBLANK(Deadline_3__c),
        ISBLANK(Deadline_4__c),
        ISBLANK(Deadline_5__c)
    ),
    NULL,
    DATE(2100, 01, 01) +
    MIN(
        IF(
            ISBLANK(Deadline_1__c) || Deadline_1__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_1__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_2__c) || Deadline_2__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_2__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_3__c) || Deadline_3__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_3__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_4__c) || Deadline_4__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_4__c
        ) - DATE(2100, 01, 01),
        IF(
            ISBLANK(Deadline_5__c) || Deadline_5__c < TODAY(),
            DATE(2100, 01, 01),
            Deadline_5__c
        ) - DATE(2100, 01, 01)
    )
)
This was selected as the best answer
Nicholas GrantNicholas Grant
I'm getting the following error:

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

Does anyone know of a more efficient version of this formula?