Find the row with the earliest Created (Entered) date that matches certain criteria

Jeff Reisman

This one was a challenge. I thought I had it solved, but an erroneous value showed up, necessitating taking the formula back to the drawing board.

The requirement: Show me the oldest Bill of Lading number that does not have a status of "Closed" or "Canceled." In another cell, show me the full Created date and time of that Bill of Lading. In a third cell, show me the Bill of Lading number together with the Created date and time.

Sheets: The formulas are present on a metrics sheet that is pulling data from our BOL Active Tracking sheet, and then the results are presented on a large dashboard screen in the warehouse.

Incorrect Solution: To find the BOL number, I first used the following:

=INDEX({BOL Active Tracking BOL Number}, MATCH(MIN(COLLECT({BOL Active Tracking Created}, {BOL Active Tracking Status}, @cell <> "CLOSED", {BOL Active Tracking Status}, @cell <> "CANCELED")), {BOL Active Tracking Created}, 0)) + ""

The problem with this was that when I had two or more BOLs that had been entered at the same date/time (date, hour, minute,) and one of them became the oldest one not yet Closed or Canceled, the formula returned whichever BOL number was lower numerically. It was not considering the Status field at this point, because it was only taking the first BOL Number where the dates matched. 

Correct Solution: A split of the formula into one to retrieve the earliest Created date that matches the criteria, and another to find the BOL Number for the row based on that result that also matches the same criteria. Additionally, a helper formula for conversion of the date value into text, and another for displaying the BOL Number and the Created date/time together in one cell.

Formula 1, Find the Date:

On the metrics sheet, in a date column called “Date,” return the earliest Created Date from BOL Active Tracking where the status is not CLOSED or CANCELED:

=MIN(COLLECT({BOL Active Tracking Created}, {BOL Active Tracking Status}, <>"CLOSED", {BOL Active Tracking Status}, <>"CANCELED"))

Result: 06/02/20

I am collecting this date value as a date format, in order for the MIN function to work properly on it, and for the following function to be able to match the Created dates.

Formula 2, Find the BOL Number:

On the metrics sheet, return the first BOL Number from BOL Active Tracking where the Created date matches the date retrieved in the first formula, and where the status is not CLOSED or CANCELED:

=INDEX(COLLECT({BOL Active Tracking BOL Number}, {BOL Active Tracking Status}, <>"CLOSED", {BOL Active Tracking Status}, <>"CANCELED", {BOL Active Tracking Created}, Date@row), 1) + ""

Result: 97553662

I am converting the result from number to text at the end of the formula using +"". This is because I am concatenating the result with the Created Date/Time that I pull into another cell using the same MIN/COLLECT code from the first function.

Formula 3, Find the Date and time in Text format:

This is the same formula as the first one, with the addition of the text conversion. Without the conversion to text, the collected Created date value only displays the date without the time portion:

=MIN(COLLECT({BOL Active Tracking Created}, {BOL Active Tracking Status}, <>"CLOSED", {BOL Active Tracking Status}, <>"CANCELED")) + ""

Result: 06/02/20 7:59 AM

Formula 4, making it pretty:

Concatenating the text-converted collected BOL Number cell and the Created date/time cell gives me a single cell with the BOL Number and the Created date/time:

=[BOL Number]@row + " - " + Created@row

Result: 97553662 – 06/02/20 7:59 AM

On the warehouse dashboard screen, the users now see the correct value, and know at a glance which BOL is getting left behind.

Stefan

Hi Jeff,

thanks for sharing with the community with all the detailed steps !

Greetings

Stefan

Prime George

Hello. I am looking for something very similar but not as complex. All I am wanting to to show the "Date Created" from the oldest to newest or even dates exceeding 30 days and display it on a dash.

Should I be entering the formula on the sheet summary? I know this is a simple solution but I just cannot get it. I thank you in advance.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/298933#Comment_298933

You should be able to do that with a report. You have multi-level sort options and filters. You can sort by date created oldest to newest, and filter out anything newer than 30 days. Report data can be displayed on a dashboard.

Prime George

Jeff,

I knew I was overthinking it! Thank you so much!