Help

Re: Consider 3-4 different date values, show the latest

Solved
Jump to Solution
1955 0
cancel
Showing results for 
Search instead for 
Did you mean: 
esu
5 - Automation Enthusiast
5 - Automation Enthusiast

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.
image

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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

See Solution in Thread

12 Replies 12

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

esu
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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

image

esu
5 - Automation Enthusiast
5 - Automation Enthusiast

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})

image

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
16 - Uranus
16 - Uranus

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

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

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

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

So, @esu, combining @Kamille_Parks’ and @kuovonne’s contributions above, this is what your resulting formula would look like.

As Kuovonne mentioned above, this would allow you to use times in your date fields as well, but would work equally fine with just dates alone:

IF(
   OR({Date 1}, {Date 2}, {Date 3}), 
   DATETIME_PARSE(
      MAX(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'x'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'x'))), 
         IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'x')))
      ), 
      'YYYYMMDD'
   )
)
esu
5 - Automation Enthusiast
5 - Automation Enthusiast

ahhh thank you so much @Kamille_Parks @kuovonne @ScottWorld !!

Can't get MAX() to Format the result as a DATE, only various number formats.
I am trying to pull the latest date from a single field, and display it in another field within the same table.