Formula to pull out nearest date from multiple date fields - Answers - Salesforce Trailblazer Community
ShowAll Questionssorted byDate Posted
Katie 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 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(
),
NULL,
DATE(2100, 01, 01) +
MIN(
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01)
)
)

Katie Silbiger
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.

Jeff May

MAX(date1,
MAX(date2,
MAX(date3, MAX(date4, date5))
)
)
Deepak Anand
```IF(
AND(
),
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 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 Anand
Alright. So I haven't tried it but worth a shot:
```IF(
AND(
),
NULL,
DATE(1900, 01, 01) +
MAX(
IF(
DATE(1900, 01, 01),
) - DATE(1900, 01, 01),
IF(
DATE(1900, 01, 01),
) - DATE(1900, 01, 01),
IF(
DATE(1900, 01, 01),
) - DATE(1900, 01, 01),
IF(
DATE(1900, 01, 01),
) - DATE(1900, 01, 01),
IF(
DATE(1900, 01, 01),
) - DATE(1900, 01, 01)
)
)```
Katie Silbiger
Hmm, in the above example, this is returning 6/5/2014.  Any ideas?
Deepak 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 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(
),
NULL,
DATE(2100, 01, 01) +
MIN(
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(
DATE(2100, 01, 01),
) - DATE(2100, 01, 01),
IF(