# Rollup formula for MIN date with possible blanks?

Topic Labels: Formulas
Solved
Jump to Solution
2165 5
cancel
Showing results for
Search instead for
Did you mean:
7 - App Architect

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.

1 Solution

Accepted Solutions
8 - Airtable Astronomer

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

5 Replies 5
8 - Airtable Astronomer

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

16 - Uranus

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.

8 - Airtable Astronomer

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:

7 - App Architect

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.

8 - Airtable Astronomer

thanks for the feedback - and strange re the difference