Rollup formula for MIN date with possible blanks?

[matt_stewart1]

I have 2 date columns that are only completed on some projects. I need this rolled up to show the MIN of each of these dates on another table.

Arrayunique shows the value, but its not a date, its text.
Any other formula like if value then min will only show 0. Essentially any time I add Min to the formula it is showing 0 because of the blank cells.

[Russell_Findlay · Accepted solution]

If i understand it correctly - you want a roll up of the minimum date value for each of 2 fields but only for all records where there is a date?

If you create a roll up field on the table (which is linking to these records)

You have a number of record in tavble 1 - some of which have dates and some don’t

These are linked to records in table 2 - and you are looking for the minimum (non blank) date…

This below seems to work for me - please let me know if i have misunderstood

[Kamille_Parks]

In the same table where your two date columns are, add a formula field that determines which of those two dates is the earliest accounting for blanks:

IF(
   OR({Date 1}, {Date 2}), 
   DATETIME_PARSE(
      MIN(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD')))
      ), 
      'YYYYMMDD'
   )
)

In your other table, create a Lookup or Rollup field pointed at that formula.

[Russell_Findlay]

Perfect - I had thought the OP meant the minimum of date 1 across all records rather than the minimum of date 1 and 2 for each record … if the latter this works perfectly - if the former then my solution may be what OP is looking for

:thumbs_up:

[matt_stewart1]

thanks both!

actually It was the former, its looking at the min value of many projects dates connected to a single rollup.

@Russell_Findlay that is how I set mine up, but its showing 0. I did find a solve by adding a filter to the rollup settings to only refer to records where this value is not empty. But its odd I needed to do this as your example shows it should work like I thought it had in past. ill have to dig in why mine is not working without the filter.

[Russell_Findlay]

thanks for the feedback - and strange re the difference