Using a formula to get the earliest/latest date across several fields

Show and Tell Tips & Tricks
1

Sometimes you want to know the latest date across several fields.
image

While there are many ways to do this, I present one way that

  • works with date/time fields as well as date fields
  • can be easily expanded to include additional date fields
  • preserves the original time, down to the millisecond
  • is blank when all the input date fields are blank

Here is a formula to get the latest date.

IF(
  OR( {date1}, {date2}, {date3} ),
  DATETIME_PARSE(
    MAX(
      IF(
        {date1},
        VALUE(DATETIME_FORMAT({date1}, "x"))
      ),
      IF(
        {date2},
        VALUE(DATETIME_FORMAT({date2}, "x"))
      ),
      IF(
        {date3},
        VALUE(DATETIME_FORMAT({date3}, "x"))
      )
    ),
    "x"
  )
)

And here is one to get the earliest date.

IF(
  OR( {date1}, {date2}, {date3} ),
  DATETIME_PARSE(
    MIN(
      IF(
        {date1},
        VALUE(DATETIME_FORMAT({date1}, "x")),
        999999999999999
      ),
      IF(
        {date2},
        VALUE(DATETIME_FORMAT({date2}, "x")),
        999999999999999
      ),
      IF(
        {date3},
        VALUE(DATETIME_FORMAT({date3}, "x")),
        999999999999999
      )
    ),
    "x"
  )
)

These formulas work by first checking that at least one of the date fields has an input. Then the formula converts each date to its Unix millisecond timestamp. The formula then takes the min or max Unix timestamp and converts it back into a date. When looking for the minimum date, the formula uses a very large number for blank dates so that the proper minimum date will be picked.

5 Likes
2

Loves this! I knew how to do MAX and MIN but not for multiple dates. That’s a great idea.

1 Like
3

This is such a fantastic & useful tip, @Kuovonne!!

Thank you for sharing these excellent formulas, and also for reminding all of us about the incredibly useful Unix millisecond timestamp. :cowboy_hat_face: :raised_hands:

On a somewhat related note to @kuovonne’s tip:

If you want to lookup a field from another table, but you only want to lookup data from whichever linked record contains the earliest/latest date, you can start with @kuovonne’s formulas in the linked table, and then use the extra tricks that I discuss in the BuiltOnAir podcast episode below.

1 Like
4

yo ! worked like a charm … thanks x 1,000,000 !!

1 Like
5

This workaround is great, however the dates I need to compare need a minor calculation included to kick out the correct final date.

For instance, I need to know which date is later {date 1} + 7 days, {date 2} + 7 days, or {date 3} - 7.

In excel, it’s pretty straightforward: =MAX($G33+7,$I33+7,$J33-7)

But when I try to add in the +/-7 days information into the formula you’ve provided, I get an error.

Is there a way around this, or is this a feature we’ll have to hold out for in the future? @Kuovonne

6

To add days, use the DATEADD() function. You cannot simply add the number. To get an earlier date, add a negative number.

DATEADD({date 1}, 7, 'days')
7

Thank you for the quick response!

I have used the DATEADD() to add days to a date, but then using that calculation within the formula you presented here, to try and get the max date with those qualifying features, the formula seems to break. Do you know of a way to get the max date result when the date columns need to utilize the DATEADD() function?

8

Post your full formula.

How is the formula breaking? If it won’t save, you probably have a syntax issue. If the results are off by a day, you may have a timezone issue with different date fields having different timezones. If the formula is breaking some other way, can you provide example data, including actual results and desired results?

9

@Kuovonne, thank you for your responses, and bearing with me as I may be out of my depth here…

This is an example of the data I’m trying to compare, and the error I received:

image
image720Ă—61 2.04 KB

When I use your formula at the top of this thread, my three date fields return 10/5/24 as the max date, which is great for comparing just those dates. The problem I’m running into is that when date3 is - 7 days compared to date2 +7 days, that would make the actual max date 10/8/2024 in this situation, which is what I’m trying to get to display in the max date column.

I tried incorporating DATEADD to get the right dates I wanted for comparison, and incorporated it into the original formula, which ended up looking like this:

IF(
  OR(DATEADD({date1}, 7, "days"), DATEADD({date2}, 7, "days"), DATEADD({date3}, -7, "days")),
  DATETIME_PARSE(
    MAX(
      IF(
        DATEADD({date1}, 7, "days"),
        VALUE(DATETIME_FORMAT(DATEADD({date1}, 7, "days"), "x"))
      ),
      IF(
        DATEADD({date2}, 7, "days"),
        VALUE(DATETIME_FORMAT(DATEADD({date2}, 7, "days"), "x"))
      ),
      IF(
        DATEADD({date3}, -7, "days"),
        VALUE(DATETIME_FORMAT(DATEADD({date3}, -7, "days"), "x"))
      )
    ),
    "x"
  )
)

I’m guessing I messed something up when adding in the DATEADD portion.

That being said, do you know if a calculation like the one I am trying to do is actually possible in airtable?

10

In your screenshot, the “date” fields are not actual dates. They are text fields with values that look like dates to a human. You need to use actual date fields.

image
image743Ă—81 1.96 KB

The formula could also be a little simpler. The OR() and IF() functions are just testing if the fields have values so you don’t actually need to add days in those instances.

image
image652Ă—593 9.17 KB

If you values are off by a day, you may also need to fiddle with the timezone settings for your fields.
image

1 Like
11

Ah, that seems to have fixed it! Thank you for all of your assistance @Kuovonne–I’m new to this and really appreciate your help!