Check if date is before/after any of a list of dates

Topic Labels: Formulas
1396 4
Showing results for 
Search instead for 
Did you mean: 
8 - Airtable Astronomer
8 - Airtable Astronomer

I have the following value 2022-06-01, 2022-12-05 and I need a formula that checks whether:

  • 2022-06-01 is after TODAY()
  • or 2022-12-05 is after TODAY()

How can I do that? Doing that with a single date (e.g: 2022-06-01) is easy, but how can I handle that for a list?

Note: 2022-06-01, 2022-12-05 is a rollup value of a one-to-many relationship, I don’t think I can use the relationship to perform any kind of calculation there, though. (I don’t see how that would help)

4 Replies 4

Hey Ambroise, as far as I know, formulas can’t loop through comma separated values, so we wouldn’t be able to do this and would have to use a script for it

I’m just guessing about your base set up, but I assume that you’ve got:

  • Table 1
    • Record 1:
      • Date value: 2022-06-01
    • Record 2:
      • Date value: 2022-12-05
  • Table 2
    • Record 3:
      • Linked field value: Record 1, Record 2
      • Rollup value: 2022-06-01, 2022-12-05

Any chance we could put the formula that checks whether the date’s after today inside Table 1, and then do a rollup with an IF, like in this base I’ve set up?
Screenshot 2022-06-17 at 12.29.03 PM

Screenshot 2022-06-17 at 12.29.07 PM

8 - Airtable Astronomer
8 - Airtable Astronomer

Thanks for the pointers, I had simplified the problem voluntarily.

I don’t think automations can help in this case (too many would be triggered at each change), and putting the formula in “Table 1” won’t help, because the formula isn’t actually a simple “is after today” comparison, but a comparison with a date that is specified elsewhere, and looked up in “Table 2”.

I was thinking about using a formula that parses each date distinclty, and checks whether any of those passes the filter (which is “is after today?”, in this example)

Ah got it. Yeah, I don’t think you can loop through comma separated values via a formula, so you wouldn’t be able to parse each date in the rollup I’m afraid

Would love to try to help create a workaround though! If you could let me know what the exact thing we’re trying to do is with the base setup I’d be happy to take a crack at it

can you use built-in Rollup filter for your case?