Finding the closest date today with 2 columns of dates

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.

Smartsheet 2 columns .png

Find more posts tagged with

Formulas

functionality

Accepted answers

Courtney S.

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.

All comments

Courtney S.

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: https://help.smartsheet.com/function/collect

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()))

Paisdais

Hi Courtney,

Thank for answering this question.

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.

DKazatsky2

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

Paisdais

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

Courtney S.

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.