Print this page

How to find the most recent date and oldest date in multiple date fields

Knowledge Article Number 000232614
Description Scenario: There are multiple date fields on a record and you would like to find the most recent date or the oldest date between all of the available date fields.
Resolution You'd need to use a Formula field to find the most recent date from multiple date fields on a record:

Click on Setup | Customize | Object | Fields | New | Formula Data type | Return type: Date | Field label | 

IF( 
AND( 
ISBLANK(Date_1), 
ISBLANK(Date_2), 
ISBLANK(Date_3), 
ISBLANK(Date_4), 
ISBLANK(Date_5) 
), 
NULL, 
DATE(1900, 01, 01) + 
MAX( 
BLANKVALUE(Date_1 , DATE(1900, 01, 01)) - DATE(1900, 01, 01), 
BLANKVALUE(Date_2, DATE(1900, 01, 01)) - DATE(1900, 01, 01), 
BLANKVALUE(Date_3 , DATE(1900, 01, 01)) - DATE(1900, 01, 01), 
BLANKVALUE(Date_4, DATE(1900, 01, 01)) - DATE(1900, 01, 01), 
BLANKVALUE(Date_5, DATE(1900, 01, 01)) - DATE(1900, 01, 01) 

)

This formula will find the recent date among the five fields from a record.



Formula to find oldest date from multiple date fields on a record:

Click on Setup | Customize | Object | Fields | New | Formula Data type | Return type: Date | Field label | 

IF( 
AND( 
ISBLANK(Date_1), 
ISBLANK(Date_2), 
ISBLANK(Date_3), 
ISBLANK(Date_4), 
ISBLANK(Date_5) 
), 
NULL, 
DATE(2998,1,1) - MAX( 
DATE(2998,1,1) - BLANKVALUE(Date_1 , DATE(2998,1,1)), 
DATE(2998,1,1) - BLANKVALUE(Date_2, DATE(2998,1,1)), 
DATE(2998,1,1) - BLANKVALUE(Date_3 ,DATE(2998,1,1)), 
DATE(2998,1,1) - BLANKVALUE(Date_4, DATE(2998,1,1)), 
DATE(2998,1,1) - BLANKVALUE(Date_5, DATE(2998,1,1)) 

)

Note: If all the fields are blank then the formula return null (-) instead of date otherwise returns with the most recent or oldest date.


See Also: 

Building Formulas
Examples of Advanced Formula Fields



 




promote demote