2

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
)
Improve this question

2 Answers 2

Reset to default
4

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
    )

From this thread:

https://success.salesforce.com/answers?id=90630000000gj0dAAA

Some additional example / options here : https://success.salesforce.com/answers?id=90630000000gu73AAA

Improve this answer
7
  • This doesn't work if date1__c is null.
    – Adrian Larson ♦
    Feb 19, 2015 at 0:05
  • I guess some if statements can be added to firstly check if any of the fields contain a value (if OR statement) and then use the above logic. Thanks this solution will work well - before I select it as my answer I want to see if anyone has a suggestion for how to do this in a workflow or a formula (no test classes/support required). If not I can tailor your example to fit my needs thanks you... FYI "return" would only work in a formula scenario ie if MATCHES CRITERIA return date
    – David Dawson
    Feb 19, 2015 at 0:10
  • @AdrianLarson - Good catch - update to account for that possibility
    – Eric
    Feb 19, 2015 at 1:14
  • @DavidDawson - Easy thing about the trigger as it is written is all you have to do to cover lines is insert/update a record. Simply inserting / updating a record will give you 100% coverage. then to properly test assert the correct value was obtained. Very easy test class to write.
    – Eric
    Feb 19, 2015 at 1:59
  • yes you are correct - despite how simple it is I always follow best practice and use formulas/workflows over triggers as much as possible but your solution is spot on. Thank you very much
    – David Dawson
    Feb 19, 2015 at 4:32
1

With the new-ish FROMUNIXTIME and UNIXTIMESTAMP functions, you can turn the date fields into numbers (unix time stamps), and feed those into the MAX function which returns a single value, and turn that back into a date.

Just tested this out and it worked perfectly on two fields, and should extend to a lot more (compile size was 161 chars only)

FROMUNIXTIME(
MAX( UNIXTIMESTAMP( fHCM2__Current_Employment__r.fHCM2__Start_Date__c ) , UNIXTIMESTAMP( fHCM2__Current_Employment__r.Youth_Engagement_Policy_Completion_Date__c )
))
Improve this answer
1
  • Have not tested for null dates yet to see how that behaves.
    – gorav
    Jun 9 at 23:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .