How can we make a judgment between each date in column A cell and the date in Column B

[Vijay_chen]

How can we make a judgment between each date in column {Directive status 1} cell and the date in Column {Calculated ETA}, and then return the “Overdue” if one judgment result is false?
Or how do I get the smallest date in column {Directive status 1} cell.

[Kamille_Parks]

You can’t iterate over arrays in Formulas. But since that array is in the form of a Lookup, that means you can accomplish this in reverse:

Add a Formula field to whatever table that Lookup is pulling from, and compare each record’s value against {Calculated ETA} (bring in a Lookup for this field if you have to). Then the table you’ve screenshotted could include a Rollup like so: IF(FIND(‘Overdue’, ARRAYJOIN(values)), ‘Overdue’, ‘Valid’).

[Vijay_chen]

Thank you, but I am not very clear for your said, can you explain more about " Add a Formula field to whatever table that Lookup is pulling from, and compare each record’s value against {Calculated ETA} (bring in a Lookup for this field if you have to)."
And I know the Rollup function from your description, so I used it to pick the minimum date of the Lookup value.
Then I have some questions below:
1, Rollup→IF(MIN(values),IF(MIN(values)>{Calculated ETA},‘Valid’,‘Overdue’)), why the return values is blank?
2, Why formula that MIN({Standards status(P1)},{Standards status(A1),{Directive status 1}}, return 0, not a date?( Based on the case where all three columns have values)
3, Why the following formula is error? (My thinking is that if 3 column are blank, return ‘Valid’, if one of date of 3 column is small than {Calculated ETA}, return ‘Overdue’)

[Kamille_Parks]

I don’t understand why your formula is suddenly so complicated. You’re referencing fields that didn’t seem involved in your original post. It also “asks” the same things several times and is generally inefficient. Once again:

1. {Directive status 1} is a Lookup field, meaning its pulling one field value from each record linked to it. What you need to do is find the source that Lookup field is looking at.
2. Next to that source/target field, add a Lookup field that pulls in the {Calculated ETA} field.
3. Next to that add a Formula field like so: IF({name of Directive Status 1's target field} > {Calculated ETA lookup field}, "Valid", "Overdue")
4. In the table that has the {Direct status 1} Lookup field, add a Rollup field that pulls in the Formula field referenced above using: IF(FIND('Overdue', ARRAYJOIN(values)), 'Overdue', 'Valid')

[Vijay_chen]

I see your said now, but it won’t succeed because if {Calculated ETA} Lookup to another table it has a lot of dates back.

[Kamille_Parks · Accepted solution]

You’ve highlighted 6 columns which don’t seem relevant to your problem, really. It’s making deciphering your issue very confusing.

Vijay_chen:

I see your said now, but it won’t succeed because if {Calculated ETA} Lookup to another table it has a lot of dates back.

Can you try to rephrase this? I know {Calculated ETA} is a Lookup, that shouldn’t matter. If you need to make a Lookup field to lookup {Calculated ETA} (a lookup of a lookup), then do that.

[Vijay_chen]

Sorry, For your said that" 2. Next to that source/target field, add a Lookup field that pulls in the {Calculated ETA} field." will get the problem of looking up a lot of dates because this links a lot of data. (like following picture).
and my question is solved, you can see the last picture, thank you for you help(very big help for idea)