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
Best Answer
-
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
-
Your next update date column is a text string. It is not being stored as a date/time stamp (and cannot be).
-
While i understand that is there a work around
-
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))
-
thank you that worked beautifully
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!