Logo

Tag Archive for dates

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!

Salesforce – let’s have a date…and time! Date Fields Missing Time Workaround

System Date Fields Missing Time

You can’t go on a date without first setting a time, right?  Well, Salesforce has found a way to have system date fields missing time information!  Have you ever wanted to know the created date & time of a record within Salesforce reports?  How about sorting a list view in ascending or descending order based on the creation or last modified date?  Well unfortunately, even though these date/time fields capture both the date and the time, only the date is displayed in reports and views.  Thankfully there is a quick and easy workaround for this issue as outlined below.

Date Fields Missing Time – The Problem

Date Fields Missing Time Without Time In Salesforce List Views

Where has the time gone? Definitely not found in Salesforce list views and reports!

Date Fields Missing Time Without Time In Salesforce Reports

Date Fields Missing Time – The Workaround

Create a formula field and be sure to select Date/Time as the type:

Create Custom Formula for Date Fields Missing Time Without Time

For the formula result, select the System field you would like to retrieve:

Set Created Date as Formula for Date Fields Missing Time Without Time

In this case, we are wanting to pull in the Created Date & Time info.

Set Created Date As Formula Result for Date Fields Missing Time Without Time

Add the new formula fields to your list views & reports and enjoy!

Dates With Times Now In Salesforce List Views

There’s the time!  Now you can easily sort in ascending or descending order!

Dates With Times Now In Salesforce Reports

 

What other tricks do you have related to date/time fields?  Let me know in the comments!