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
)