MIn and Collect fuction not returning value

Options


=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


Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!