Logo

Tag Archive for reports

How to get the MAX or Most Recent Date of several date fields in Salesforce Formula

Goal: Return the MAX or Most Recent Date of several dates

Recently I needed to get the most recent date of 4 separate date fields.  I created a new formula field and was shocked to find that MAX(custom_date_1,custom_date_2,custom_date_3,custom_date_4) did not work.  MYSQL allows MAX() to function for date fields and I was surprised it didn’t work here!

Workaround: MAX(DATE) Formula

The full formula example of the workaround is below.  An important aspect of the formula is to handle if any of the dates that are being compared are null.  Also remember to add DATEVALUE() for any fields that are of type DATETIME.

IF(
    AND(
        ISBLANK(custom_date_1),
        ISBLANK(custom_date_2),
        ISBLANK(custom_date_3),
        ISBLANK(custom_date_4)
    ),
    NULL,
    DATE(1900, 01, 01) +
    MAX(
        BLANKVALUE(
            custom_date_1, 
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01),
        BLANKVALUE(
            custom_date_2,
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01),
        BLANKVALUE(
            custom_date_3,
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01),
        BLANKVALUE(
            custom_date_4,
            DATE(1900, 01, 01)
        ) - DATE(1900, 01, 01)
    )
)

I hope you find this helpful!