0

I am trying to write a formula that returns the minimum date (or earliest) out of these three date fields:

MIN(
Year(Year_One_Start_Date__c),
Year(Year_Two_Start_Date__c),
Year(Year_Three_Start_Date__c)
)

I get an error when I run check syntax in the formula builder that says:

Error: Formula result is data type (Number), incompatible with expected data type (Date).

I tried wrapping the formula in the DateValue function, but that didn't work either.

Example Dates:

12/10/2022 - Year_One_Start_Date__c
12/15/2022 - Year_Two_Start_Date__c
12/18/2022 - Year_Three_Start_Date__c

Based on this sample data, the result of the formula would return a date value of 12/10/2022

How can I modify my existing formula and get the correct output?

1 Answer 1

2

Well, the error should give you a decent hint as to why it's not working.

MIN() takes number arguments, and returns a Number.
You configured your formula to return a Date.
Since Number != Date, you get an error

Even if you were able to get the minimum year (assuming that the years of all 3 dates are different, which they aren't in your example), that doesn't tell you which date to pull the month and day components from.

It's probably possible to get the minimum date using that approach, but MIN() isn't really the right tool for this job.

Instead, just use the normal comparison operators (<, <=, >, >=, ==, !=). They can be used with Dates and Datetimes. Since you only have 3 dates, doing this with comparison operators and IF() is simple enough.

  • Check if date 1 < date 2
    • if true, then check if date 1 < date 3
      • if true, return date 1
      • else return date 3
    • else check if date 2 < date 3
      • if true, return date 2
      • else return date 3

Going much beyond that though would be really unwieldy. Formulas are only really suited for the most simple of date/time manipulations. In that case, using a before-save record-triggered flow would be a better idea.

+edit:
After another look through Formula Operators and Functions By Context, UNIXTIMESTAMP() could help out here. It turns Dates/Datetimes into a number (seconds since 1970-01-01) which you could feed into MIN(). You could then feed the result of MIN() into CASE() to get back to a Date/Datetime.

A bit of a roundabout solution, but it would scale to higher numbers of Date/Datetime fields (though really, a flow would probably still be better). As a pseudo-formula

CASE(
    /* first arg, returns the minimum date/datetime as a number */
    MIN(UNIXTIMESTAMP(date 1), UNIXTIMESTAMP(date2), ..., UNIXTIMESTAMP(date n)),
    /* each subsequent argument pair maps a timestamp back to its date */
    UNIXTIMESTAMP(date 1), date 1,
    UNIXTIMESTAMP(date 2), date 2,
    ...
    UNIXTIMESTAMP(date n), date n,
    null /* default value, required */
)

Using CASE() is required for this approach (at time of writing) because Salesforce doesn't give us a function to go from a timestamp back to a date/datetime (and doing the calculations manually would become unwieldy).

You must log in to answer this question.

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