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.

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.

Sorry, it’s late here & I’m about to go to sleep, so I’m just being lazy by linking to my video. :stuck_out_tongue_winking_eye: But in the future, I’ll come back & type out the full instructions here!