MIn and Collect fuction not returning value

tink012184

=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

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

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.

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

tink012184

thank you that worked beautifully

Paul Newcome

Happy to help. 👍️