Roll up most recent date that isn’t in the future


#1

I’m relatively new to airtable so thanks in advance for any help.

I want to show he equivalent of the ‘last contact’ rollup that is on the Sales CRM but exclude dates that might exist in the future.

I got as far as creating a field on the same table as the dates I’m interested in which filters out future dates entirely but rolling this up to show a max value doesn’t seem to work (because it is a formula field not a date field?)

So I think the answer to this might lie in rollup that uses an aggregate formula rather than function but I am looking for pointers on what that would look like.

thanks


#2

The MAX(values) formula in the rollup field worked for me when I tried to do this.

This is the formula I used in the field to filter out future dates:

IF(Date>TODAY(),BLANK(),Date)

Using BLANK() in this formula is crucial.


#3

Excellent.

So the thing that appeared to be interfering with this was the use of DATETIME_FORMAT in the filtering field to strip out the time that is in the raw date field. Now that I have removed that formatting, the roll up does work correctly.

Thanks again for the solution.