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

GMichal

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.

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

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!