Help With Using LARGE in a Date Modified Column

MWilkesen

Does anyone have experience with using LARGE in a formula to return results with latest Date Modified? I am needing to return a string that ends in the date & time a contract is marked as complete. In this instance I am looking to return a string for the 5th newest contract completion date (I am compiling the top 5 if that matters).

ISSUES:

1. The formula seems to be "buggy" it works if I am just joining a string of the top 5 dates, but will not return a single value date.
   1. Example: =LARGE([Contract Complete]:[Contract Complete], 1) + ", " + LARGE([Contract Complete]:[Contract Complete], 9); this returns 11/04/24 11:05 AM, 11/04/24 9:11 AM; as expected
   2. Example: =LARGE([Contract Complete]:[Contract Complete], 1); this returns "#INVALID COLUMN VALUE, not the expected 11/04/24 11:05 AM
2. If there is more than one line with the same timestamp, the formula returns the results as a blank.

This is the string I am using with the expected return of: Name of company, Request ID, Contract Complete Date & Time (Tennessee Medicine, REQID_08663, 11/04/24 11:05 AM), but the formula is returning a blank.

=JOIN(COLLECT([Name of Company Requesting Access]:[Name of Company Requesting Access], [Request Status]:[Request Status], "Contract Complete", [Contract Complete]:[Contract Complete], LARGE([Contract Complete]:[Contract Complete], 5)), ", ") + ", " + JOIN(COLLECT([Request ID]:[Request ID], [Request Status]:[Request Status], "Contract Complete", [Contract Complete]:[Contract Complete], LARGE([Contract Complete]:[Contract Complete], 5)), ", ") + ", " + JOIN(COLLECT([Contract Complete]:[Contract Complete], [Request Status]:[Request Status], "Contract Complete", [Contract Complete]:[Contract Complete], LARGE([Contract Complete]:[Contract Complete], 5)), ", ")

I use this exact same formula concept in another sheet, but instead of looking up the values IN the same sheet, I am looking them up from a REFERENCE sheet, and the formula works like a charm 100% of the time.

=JOIN(COLLECT({EHR | Company}, {EHR | Status}, "INTAKE - Processing Request", {EHR | Submitted Date}, LARGE({EHR | Submitted Date}, 1)), " - ") + "- " + JOIN(COLLECT({EHR | State}, {EHR | Status}, "INTAKE - Processing Request", {EHR | Submitted Date}, LARGE({EHR | Submitted Date}, 1)), " - ") + " - " + JOIN(COLLECT({EHR | Submitted Date}, {EHR | Status}, "INTAKE - Processing Request", {EHR | Submitted Date}, LARGE({EHR | Submitted Date}, 1)), " - ")

Any advice for this complex question?

Thanks,

Michael

Hunter Taylor

Hi Michael,

To address these issues, we'll restructure the formula to ensure it correctly retrieves and formats the desired data, even in the presence of duplicate dates. Here's a step-by-step approach:

1. Use Helper Columns to Rank the Dates

Since Smartsheet's formula capabilities are somewhat limited compared to full-fledged spreadsheet software, introducing helper columns can simplify the process.

• Add a new column, say [Rank], to your sheet. This column will rank the [Contract Complete] dates in descending order (latest first).
  • Formula for [Rank]:
    • =IF([Request Status]@row = "Contract Complete", RANK([Contract Complete]@row, [Contract Complete]:[Contract Complete], 0), "")
      • Explanation:
        • IF([Request Status]@row = "Contract Complete", ...): Ensures only rows with "Contract Complete" status are ranked.
        • RANK([Contract Complete]@row, [Contract Complete]:[Contract Complete], 0): Assigns a rank to the date, with 0 indicating descending order.

2. Retrieve the 5th Latest Date

Now that you have a ranked list, retrieving the 5th latest date becomes straightforward.

• =IFERROR(LARGE([Contract Complete]:[Contract Complete], 5), "N/A")
  • Explanation:
    • LARGE([Contract Complete]:[Contract Complete], 5): Retrieves the 5th largest date.
    • IFERROR(..., "N/A"): Handles cases where there are fewer than 5 completed contracts.

3. Collect and Concatenate Relevant Data

With the helper column in place, you can now construct the desired string by collecting data where the [Rank] is 5.

• =JOIN(COLLECT([Name of Company Requesting Access]:[Name of Company Requesting Access], [Rank]:[Rank], 5), ", ") + ", " + JOIN(COLLECT([Request ID]:[Request ID], [Rank]:[Rank], 5), ", ") + ", " + TEXT (LARGE([Contract Complete]:[Contract Complete], 5), "MM/DD/YY hh:mm AM/PM")
  • Explanation:
    • First JOIN + COLLECT: Retrieves and concatenates the names of companies with a rank of 5.
    • Second JOIN + COLLECT: Retrieves and concatenates the corresponding Request IDs with a rank of 5.
    • TEXT(LARGE(...), "MM/DD/YY hh:mm AM/PM"): Formats the 5th latest [Contract Complete] date and time.

4. Handle Duplicates Gracefully

If multiple entries share the same [Contract Complete] timestamp, the rank might assign the same number to multiple rows. To ensure the formula doesn't return blanks:

• Modify the [Rank] formula to account for ties by adding a small unique identifier, such as the row number.
  • Updated [Rank] Formula:
    • =IF([Request Status]@row = "Contract Complete", RANK([Contract Complete]@row, [Contract Complete]:[Contract Complete], 0) + (ROW() / 10000),"")
      • Explanation:
        • ROW() / 10000: Adds a tiny fraction based on the row number to differentiate between duplicates, ensuring each has a unique rank.

By restructuring your approach to incorporate helper columns for ranking and ensuring proper handling of duplicates and data types, you can achieve the desired outcome. This method not only resolves the current issues but also makes the formula more maintainable and easier to debug in the future.

Feel free to reach out if you need further assistance or clarification!