MIn and Collect fuction not returning value

=MIN(COLLECT({RAW NOC INC Data Range 1}, {RAW NOC INC Data Range 5}, "Outage", {RAW NOC INC Data Range 6}, "Mountain States"))

I am using the above formula and it is returning a 0 and not the oldest update time. When i change it to date as the format i get nothing but a blank spot.

Catch is i want the date and the time from the original sheet i just want the oldest one based off certain criteria.

Results as is

Range 1

Range 5

Range 6

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You can use this in a helper column to generate a number based on the yyyymmddhhmmss stamp and use a MIN/COLLECT to pull the lowest number and drop that into an INDEX/MATCH.

=VALUE(MID(NextUpdateDate@row, FIND(" ", NextUpdateDate@row) - 4, 4) + LEFT(NextUpdateDate@row, FIND("/", NextUpdateDate@row) - 1) + MID(NextUpdateDate@row, FIND("/", NextUpdateDate@row) + 1, FIND("/", NextUpdateDate@row, FIND("/", NextUpdateDate@row) + 1) - (FIND("/", NextUpdateDate@row) + 1)) + "" + (VALUE(MID(NextUpdateDate@row, FIND(" ", NextUPdateDate@row) + 1, FIND(":", NextUpdateDate@row) - (FIND(" ", NextUPdateDate@row) + 1))) + IF(FIND("P", NextUpdateDate@row)>0, IF(MID(NextUpdateDate@row, FIND(" ", NextUPdateDate@row) + 1, FIND(":", NextUpdateDate@row) - (FIND(" ", NextUPdateDate@row) + 1)) <> "12", 12, 0), IF(MID(NextUpdateDate@row, FIND(" ", NextUPdateDate@row) + 1, FIND(":", NextUpdateDate@row) - (FIND(" ", NextUPdateDate@row) + 1)) <> "12", 0, -12))) + "" + MID(NextUpdateDate@row, FIND(":", NextUpdateDate@row) + 1, 2) + MID(NextUpdateDate@row, FIND(":", NextUpdateDate@row) + 4, 2))

All comments

Paul Newcome

Your next update date column is a text string. It is not being stored as a date/time stamp (and cannot be).

tink012184

While i understand that is there a work around

Paul Newcome

You can use this in a helper column to generate a number based on the yyyymmddhhmmss stamp and use a MIN/COLLECT to pull the lowest number and drop that into an INDEX/MATCH.

tink012184

thank you that worked beautifully

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