Help

Find the "Next Date" from a series of dates in a rollup / lookup / array

896 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Frazer_McLeod
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everyone! We were stumped on this for a while and couldn't find any workarounds. So here's how to extract the next date in a series of dates inside of an array or a rollup from another table.

1. Convert your dates to UNIX  and wrap them up in a VALUE so that they are all fixed numbers

VALUE(DATETIME_FORMAT([date], 'x')

2. In the table where you're wanting to store & retrieve the "Next date" in a series, create a lookup of the column you just created in step 1 & chuck a filter on it that says "On or after Today"

3. Create a new formula column and retrieve the 'minimum UNIX date' with a MIN() 

MIN({Your Lookup Column Name})

4. Convert step 3 back to your desired date with a DATEADD() formula

DATEADD('1/1/1970',{Your column from step 3},'seconds')

 Voila, you now have the "next upcoming date" in a series of dates.

0 Replies 0