Finding the most recent and next dates

[Zac_Traeger]

Is it possible to use a formula or some other work around to find the most recent date across 4 rollup columns? See photo. I'd like the left column to then show the most recent date, the 4 columns are different donation platforms and it'd be helpful to summarize that info to one single column showing most recent date regardless of the donation platform. 

[pressGO_design]

Step 1: Calculate the most recent date as the number of days before today. I called mine "Most Recent Date of All"

MAX(DATETIME_DIFF({Date 1 Rollup},TODAY(), 'd'), DATETIME_DIFF({Date 2 Rollup},TODAY(), 'd'), DATETIME_DIFF({Date 3 Rollup}, TODAY(),'d'))

Step 2: Subtract that number of days from today

DATEADD(TODAY(), {Most Recent Date of all}, 'd')

Here it is in one big happy formula

DATEADD(TODAY(),MAX(DATETIME_DIFF({Date 1 Rollup},TODAY(), 'd'), DATETIME_DIFF({Date 2 Rollup},TODAY(), 'd'), DATETIME_DIFF({Date 3 Rollup}, TODAY(),'d')), 'd')

[Zac_Traeger]

getting closer! Thanks. It appears that the expected behavior works (day +1) when both columns have data, but when there is data in only one or the other then the result is todays date. 

[kuovonne · Accepted solution]

I don't recommend using a formula that involve the TODAY() function. That function can be resource intensive because it recalculate every few hours even if no other data has changed.

I recommend the formula described here.

[pressGO_design]

Thanks for the info about the TODAY() function. I didn't know that. Appreciate it.