Collect a Value from a Column if Today's Date Falls Between Two Dates

I am attempting what I thought was a simple formula to collect a value from the 'Week" column if today's date falls between a Start Date and an End Date. Here's what I have so far which returns an #INVALID OPERATION result:

=COLLECT(Week:Week, Week:Week, AND(TODAY() >= [Start Date]:[Start Date], TODAY() <= [End Date]:[End Date]))

Here is a screenshot of the column layout:

If the formula was working correctly with today being 03/06/25, the value that I would expect to be returned is '03/10/25'. Is COLLECT the appropriate function to use to achieve this result?

I've tested out the 'Criterion1' portion of the formula and it appears to be correct:

AND(TODAY() >= [Start Date]:[Start Date], TODAY() <= [End Date]:[End Date])

but I must be missing something on the remaining portion.

Find more posts tagged with

collect

Accepted answers

Isis Taylor

@GMichal The issue is that the COLLECT function can not be used alone and must be used within another function. Adding JOIN should fix it.

=JOIN(COLLECT(Week:Week, [Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date], >=TODAY()))

All comments

Isis Taylor

@GMichal The issue is that the COLLECT function can not be used alone and must be used within another function. Adding JOIN should fix it.

=JOIN(COLLECT(Week:Week, [Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date], >=TODAY()))

GMichal

That was it. Thank you, Isis. Just when I think I am progressing to intermediate knowledge of Smartsheet, I am humbled to know that there is so much more I don't know!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.