COLLECT with Date criteria

Hello,

Here is the formula I need help with please:

=COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},= "FE Sign off required (On QASOR)", ({2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)))

{2046 - B1 Gantt Project Plan WPincomments} is the column where the data I want to collect is

{2046 - B1 Gantt Project Plan Task} is the column where my 1st criteria can be found

= "FE Sign off required (On QASOR)" is my 1st criteria

{2046 - B1 Gantt Project Plan Date} is the column where my 2nd criteria can be found, a date column

< DATE(2023, 7, 28) is my second criteria

Thank you

Find more posts tagged with

Accepted answers

Nick Korna

You need to make a few alterations for JOIN to work:

=JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},"FE Sign off required (On QASOR)", {2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)), "-")

This should give you all the results in a single cell, separated by dashes.

An example of it in action (with columns rather than cross sheet refs):

Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! 🙂

All comments

KarineMul22_TPg

As COLLECT doesn't work on its own, I have added JOIN but still has the same issue

=JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},="FE Sign off required (On QASOR)", ({2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28))), "-")

Nick Korna

You need to make a few alterations for JOIN to work:

=JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},"FE Sign off required (On QASOR)", {2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)), "-")

This should give you all the results in a single cell, separated by dashes.

An example of it in action (with columns rather than cross sheet refs):

Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! 🙂

KarineMul22_TPg

Thank you Nick, but it's still coming back with #UNPARSEABLE :(

I suspect it's the date part but cannot find the right way

Nick Korna

There was an extra bracket in there (just before the date range/criteria) - try the (now edited) formula.

KarineMul22_TPg

Brilliant, it works! Many thanks

Nick Korna

No problem, glad to have helped!

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.