MIN & MAX Collect Formula Help

Smar Czar

I am trying to write a cross sheet formula that evaluates dates based on a matching project ID on the source sheet and return a date value in the target sheet (where the formula is being written).

The formula should:

Look up dates in a column on another (source) sheet. Each row on this sheet has a unique ID.

Only evaluate those dates who share a unique ID as the ID on the target sheet

If you find a match and the date(s) is in the past relative to today, take the date from the past that is closest to today.

If the date(s) is in the future, take the date that is nearest to today but in the future.

If the date in question is today then show that date.

If you evaluate the of dates in the source and some dates are in the past and some dates are in the future, then, consider the column called "MS Complete". if the column "MS complete" is checked, then take the date in that row (be it in the future or the past). otherwise take the date that is the closest date to today but in the future.

I started (with your help) - to write a formula that brings in the next most recent date in the future……can this be used as a jumping off point to write the rest of the formula?

=MIN(COLLECT({OPS Project Milestone Tracker V2 Doc Signed}, {OPS Project Milestone Tracker V2 Doc Signed}, >=Today@row , {OPS Project Milestone Tracker V2 Requirement Name}, [Requirement Name]@row ))

jmyzk_cloudsmart_jp

Hi @Smar Czar

Suppose we have a source sheet as the one at the bottom. (I created a report to group by Project ID)

https://app.smartsheet.com/b/publish?EQBCT=b9ae390f5b1045cdb71337a37f7bc94e

We can summarise your condition as follows;

TODAY? 
  YES: TODAY (1)

  NO:MS COMPLETE?  
    YES: The Date (2)

    NO:Has Future Date?    
      Yes: Nearest Future Date (3)

      No: Nearest Past Date (4)

Let's create a formula for each.

1. If the date in question is today then show that date.

[Today]
=IF(HAS(COLLECT({Source Sheet : Date}, {Source Sheet : Project ID}, [Project ID]@row), TODAY()), TODAY())

2. If you evaluate the of dates in the source and some dates are in the past and some dates are in the future, then, consider the column called "MS Complete". if the column "MS complete" is checked, then take the date in that row (be it in the future or the past).

[Complete]
=IF(COUNTIF(COLLECT({Source Sheet : MS complete}, {Source Sheet : Project ID}, [Project ID]@row), true) > 0,
  INDEX(
    COLLECT({Source Sheet : Date},
      {Source Sheet : Project ID},[Project ID]@row,
      {Source Sheet : MS complete},true)
  , 1))

3. otherwise take the date that is the closest date to today but in the future.

[Future MIN]
=IF(COUNT(
      COLLECT({Source Sheet : Date}, 
        {Source Sheet : Date}, >TODAY(),
       {Source Sheet : Project ID}, [Project ID]@row)
    ) > 0, 
  MIN(
    COLLECT({Source Sheet : Date}, 
      {Source Sheet : Date}, >TODAY(), 
      {Source Sheet : Project ID}, [Project ID]@row)
  ))

4. If you find a match and the date(s) is in the past relative to today, take the date from the past that is closest to today.

[Past MAX}
=MAX(
  COLLECT({Source Sheet : Date}, 
    {Source Sheet : Date}, <TODAY(), 
    {Source Sheet : Project ID}, [Project ID]@row))

Then, combine 1 to 4 with nested IFs.

[Date]

=IF(HAS(COLLECT({Source Sheet : Date}, {Source Sheet : Project ID}, [Project ID]@row ), TODAY()), TODAY(), IF(COUNTIF(COLLECT({Source Sheet : MS complete}, {Source Sheet : Project ID}, [Project ID]@row ), true) > 0, INDEX(COLLECT({Source Sheet : Date}, {Source Sheet : Project ID}, [Project ID]@row , {Source Sheet : MS complete}, true), 1), IF(COUNT(COLLECT({Source Sheet : Date}, {Source Sheet : Date}, >TODAY(), {Source Sheet : Project ID}, [Project ID]@row )) > 0, MIN(COLLECT({Source Sheet : Date}, {Source Sheet : Date}, >TODAY(), {Source Sheet : Project ID}, [Project ID]@row )), MAX(COLLECT({Source Sheet : Date}, {Source Sheet : Date}, <TODAY(), {Source Sheet : Project ID}, [Project ID]@row )))))

https://app.smartsheet.com/b/publish?EQBCT=42364998a33348c7b8a9effc40c765a8