Join/Collect Date Function with certain search Criteria

Hello,

I am using a Join/Collect function to bring a date from the source sheet to a metric sheet to drive notifications on. I'm searching for certain assets when a new entry is added. When a new entry is added, it collects the dates but puts them in the same cell and wont allow other formulas to run.

My question is: How to have it only display the most recent date from the most recent entry?

Here is my formula:

=JOIN(COLLECT({Level Green Approved Field Reports Range 1}, {Level Green Approved Field Reports Range 2}, [Primary Column]@row))

Here is a pic of the Metric Sheet:

Here is the Source Sheet:

So, I can get the date as its entered and it copies. I only want the most recent entry to display in the "End Date" column on the first sheet.

Thank you in advance!

Chris

Find more posts tagged with

Accepted answers

Paul Newcome

Try replacing the JOIN function with a MAX function.

All comments

Paul Newcome

Try replacing the JOIN function with a MAX function.

Chris Clem

Yes this worked. I changed my formula to the following:

=MAX(COLLECT({Level Green Field Reports Range 1}, {Level Green Approved Field Reports Range 2}, [Primary Column]@row))

Jen Lange

@Chris Clem , I'm left scratching my head.

My thought process was to use a MAX function on an IF statement referencing an array, but this function doesn't seem to work within Smartsheet. Then I used the delimiter in the JOIN function to result in a string of comma separated values, hoping that I could use the MAX function on the resulting string, but I need to convert the string into date values, which I also can't seem to accomplish in Smartsheet.

So, basically, I'm no help. But I'll keep thinking on this and let you know if I come across a solution.

Chris Clem

So I changed to the following:

=MAX(COLLECT({Level Green Field Reports Range 1}, {Level Green Approved Field Reports Range 2}, [Primary Column]@row))

This is now looking at the most recent date and moving it to my Metric sheet in the Exhibit as new entries are added.

Chris

Jen Lange

https://community.smartsheet.com/discussion/comment/289314#Comment_289314

Haha. I was on the right path, but missed the simple answer. I appreciate having the answer @Paul Newcome 😊

Paul Newcome

Happy to help. 👍️

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.