Consider 3-4 different date values, show the latest

[esu]

Hi everyone, I need to have a “Filter” column that is pulling date values from 3-4 different columns and selecting the one that is the most recent. So if we have 3/10/2021, 3/10/2022 and 3/10/2023, the date that populates the “Filter” column is 3/10/2023. It’s not always consistent which column will have the most updated date.

Is that possible to write a formula for?

Update:
MAX () doesn’t seem to work for anything other than Number fields.
Desired outcome is to look at multiple Date column values, pull latest date value or if all the values are the same, output the same value.

[ScottWorld]

The MAX() function will work for that, even though the formula reference page only states that it works with numbers.

[esu]

Thanks scott – do you have further guidance on the formula itself?
I tried this and it just gave me 0

MAX({Job Information: Date}, {End Date (est)}, {Termination Date})

[esu]

Am I missing something? I’m testing with straight number values and still getting 0 with

MAX({Numbers 1},{Numbers 2})

[esu]

Ah- ok @ScottWorld the Field Type must be set to Numbers in order for the MAX() to work. So it looks like Dates or even Single Line Text types don’t work. (unless you know a way to make it so it does that isn’t just MAX({Numbers 1},{Numbers 2})

[ScottWorld]

Oh sorry, my bad… i was thinking of MAX(values) in a rollup field, which actually DOES work with date fields!

There are a few different ways of writing formulas to compare all the date fields, but I don’t think there’s an “easy/quick” formula to do this. I could be wrong about this — if so, I’d love for others to please chime in and correct me with the easiest & quickest formula for accomplishing this! :grinning_face_with_big_eyes:

In the meantime, I can currently only think of complicated formulas to accomplish this.

One way would be to write a lengthy IF() statement that compares all the dates amongst each other.

Another way would be to still use the MAX() function, but since the MAX() function requires numbers, you could to convert your date fields to numbers… something like the formula below would work with 3 date fields.

Notice that I have a final date hardcoded in as 20000101 (that’s “YYYYMMDD” format, so that’s January 1, 2000). You can put whatever date you want here, but this would be the default date that would show up if none of the other 3 dates are filled in. Make it an earlier date than the other fields.

DATETIME_PARSE(
  MAX(
    IF({Date 1},VALUE(YEAR({Date 1}) & RIGHT("0" & MONTH({Date 1}),2) & RIGHT("0" & DAY({Date 1})),2)),
    IF({Date 2},VALUE(YEAR({Date 2}) & RIGHT("0" & MONTH({Date 2}),2) & RIGHT("0" & DAY({Date 2}),2))),
    IF({Date 3},VALUE(YEAR({Date 3}) & RIGHT("0" & MONTH({Date 3}),2) & RIGHT("0" & DAY({Date 3}),2))),
    20000101
    ),
'YYYYMMDD'
)

[Kamille_Parks · Accepted solution]

An alternate formula structure which doesn’t use a default date and instead returns a blank cell if neither date field is filled:

Find the maximum of three date fields on one table

IF(
   OR({Date 1}, {Date 2}, {Date 3}), 
   DATETIME_PARSE(
      MAX(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), 
         IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD')))
      ), 
      'YYYYMMDD'
   )
)

[ScottWorld]

Even better! Much simpler! Thank you, @Kamille_Parks! :grinning_face_with_big_eyes:

[kuovonne]

I use a variation of this formula. But I often use the format specifier ‘x’, especially if there might be a time involved.

[ScottWorld]

Even simpler! And way more powerful! Love it!!