Using DATEADD() for just one value in a multi-value column

[julieqiu]

Hi all,

I want to use the DATEADD() formula on only the oldest created value (date) in a Lookup column that stores multiple values (a 1-to-many relationship is needed). I can’t seem to figure out a way to design the base or write a formula that does this. Any ideas?

Thanks,
Julie

[Jeremy_Oglesby · Accepted solution]

Hi @julieqiu

Try using a Rollup field that looks up the same data as your lookup field and uses a formula construct like this as the Rollup formula:

DATEADD(MIN(values), … )

[kuovonne]

Jeremy_Oglesby:

DATEADD(MIN(values), … )

When I last tried a formula like this in a rollup field, it did not work. I had to put MIN(values) in the rollup field, and then do the DATEADD in a separate formula field. When I contacted support about the issue, support confirmed that it was necessary to use a separate formula field. I have found workarounds that involve using DATETIME_PARSE in the rollup field, but I don’t like it.