[SOLVED] In REDCap: is there a function to extract to a minimum or maximum of several different dates?

In REDCap: is there a function to extract to a minimum or maximum of several different dates?

I am looking for a possibility to extract the minimum or maximum date in REDCap. I have multiple dates (each from a different event) for a patient and need to find the most recent date.

Assuming I have two different dates: [1y_arm_1][date] and [5y_arm_1][date]. I tried the following to get the smaller of the two:

I tried to apply the following code to a text field validated as date:
@CALCDATE(min([1y_arm_1][date], [5y_arm_1][date]))
@CALCTEXT(min([1y_arm_1][date], [5y_arm_1][date]))

And I tried to apply the following code to a calculated field:
min([1y_arm_1][date], [5y_arm_1][date])

But nothing seems to display anything. Appreciate your help.

Solution

You can get what you want by using the datediff function to find how far each date is from today, the max function to find the furthest date, and @CALCDATE to retrieve the date of interest.

@CALCDATE(
    'today',
    max(
        datediff([1y_arm_1][date], 'today', 'd'),
        datediff([5y_arm_1][date], 'today', 'd')
    ) * -1,
    'd'
)

It shouldn't matter if some of the dates are blank.