# Calculate MAX between group of date fields

I have seen several posts about this, but all posts manage to successfully calculcate which date is the MAX (the one closest to today) but do not then return this date, only the number of days between that day and today which is of no use to me...

Long story short I have 5 date fields on an obj - lets say date1__c, date2__c, date3__c, date4__c & date5__c.

When a record is inserted or updated, I need to record which date is the most recent out of the 5 and return this date. From an integer perspective the same issue would be solved by the MAX formula function but this does not support dates...

Can this be achieved? Preferably in a workflow or a formula field?

UPDATE:

Going off Eric's solution below, I was able to put together this workflow field update which after a bit of tweaking works perfectly. For future ref (and anyone who comes across this post):

``````CASE
(max(
(BLANKVALUE(Date_1__c, TODAY()-365) - TODAY()),
(BLANKVALUE(Date_2__c, TODAY()-365)- TODAY()),
(BLANKVALUE(Date_3__c, TODAY()-365)- TODAY()),
(BLANKVALUE(Date_4__c, TODAY()-365)- TODAY())
)
, (BLANKVALUE(Date_1__c, TODAY()-365)) - TODAY(), Date_1__c
, (BLANKVALUE(Date_2__c, TODAY()-365)) - TODAY(), Date_2__c
, (BLANKVALUE(Date_3__c, TODAY()-365)) - TODAY(), Date_3__c
, (BLANKVALUE(Date_4__c, TODAY()-365)) - TODAY(), Date_4__c
,null
)
``````

Not sure what you mean by "return" the value as a trigger does not return anything but you can figure it out with a bit of logic

``````for(sObject r : trigger.new){
date maxDate = date1__c;

if(maxDate == null || (r.date2__c != null && r.date2__c > maxDate)) maxDate = r.date2__c;
if(maxDate == null || (r.date3__c != null && r.date3__c > maxDate)) maxDate = r.date3__c;
if(maxDate == null || (r.date4__c != null && r.date4__c > maxDate)) maxDate = r.date4__c;
if(maxDate == null || (r.date5__c != null && r.date5__c > maxDate)) maxDate = r.date5__c;
}
``````

you could most likely do the same thing in a formula using nested if's using the same logic

I found this posted as an example of a workflow field update:

``````CASE
(max(
(BLANKVALUE(date_0__c, now()-36500) - now()),
(BLANKVALUE(date_1__c, now()-36500)- now()),
(BLANKVALUE(date_2__c, now()-36500)- now()),
(BLANKVALUE(date_3__c, now()-36500)- now()),
(BLANKVALUE(date_4__c, now()-36500)- now()),
(BLANKVALUE(date_5__c, now()-36500)- now()),
(BLANKVALUE(date_6__c, now()-36500)- now()),
(BLANKVALUE(date_7__c, now()-36500)- now())
)
, (BLANKVALUE(date_0__c, now()-36500)) - now(), date_0__c
, (BLANKVALUE(date_1__c, now()-36500)) - now(), date_1__c
, (BLANKVALUE(date_2__c, now()-36500)) - now(), date_2__c
, (BLANKVALUE(date_3__c, now()-36500)) - now(), date_3__c
, (BLANKVALUE(date_4__c, now()-36500)) - now(), date_4__c
, (BLANKVALUE(date_5__c, now()-36500)) - now(), date_5__c
, (BLANKVALUE(date_6__c, now()-36500)) - now(), date_6__c
, (BLANKVALUE(date_7__c, now()-36500)) - now(), date_7__c
,null
)
``````

• This doesn't work if `date1__c` is `null`. – Adrian Larson Feb 19 '15 at 0:05