Finding the closest date today with 2 columns of dates

Options

Hi,

I am working on a formula to show the next date from 2 rows of dates. I tried =MAX(COLLECT([Release]:[Release], [Live]:[Live], >TODAY())) - and it showed a date from the past.

I tried

=MIN(COLLECT([Release]:[Release ], [Live]:[Live], >TODAY()))

With the same results.

Any help is much appreciated.

Tags:

• ✭✭✭✭✭
Options

OK, try this: =MIN(MIN(COLLECT(Release:Release, Release:Release, >TODAY())), MIN(COLLECT(Live:Live, Live:Live, >TODAY())))
And make sure the column type for that is Date.

• ✭✭✭✭✭
Options

The COLLECT Function needs both the values range and the criterion range to be given before the criterion, and then can do it again for another values range. Right now your COLLECT function is returning the min/max Release date from rows where Live dates are greater than today.
See the help page here:

If you're trying to get the minimum date after today among all the Release and Live dates listed in those twocolumns, try something more like: MIN(COLLECT([Release]:[Release], [Release]:[Release], >TODAY(), [Live]:[Live], [Live]:[Live], >TODAY()))

• Options

Hi Courtney,

I had the collect function in the formula. I tried the formula you gave me and on both accounts it returned with #INVALID OPERATION - I am not sure why but the brackets keep getting removed from the formula every time I hit enter.

• ✭✭✭✭✭
Options

Hi @Paisdais,

I believe this will give you what you are looking for.

=MIN(MIN(COLLECT(Release:Release, Release:Release, >TODAY()), MIN(COLLECT(Live:Live, Live:Live, >TODAY()))))

Hope this helps,

Dave

• Options

Thank you @DKazatsky2 - I tried the formula and got INVALID OPERATION. Did I do something wrong when entering the formula??

• ✭✭✭✭✭