+17I 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.
Best answer by Russell_Findlay
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
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
+27In 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.
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.
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:
+17thanks 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.
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.
thanks for the feedback - and strange re the difference
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.