Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 03, 2022 08:15 AM
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.
Solved! Go to Solution.
Nov 03, 2022 01:51 PM
An alternate formula structure which doesn’t use a default date and instead returns a blank cell if neither date field is filled:
Nov 03, 2022 09:21 AM
The MAX()
function will work for that, even though the formula reference page only states that it works with numbers.
Nov 03, 2022 10:57 AM
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})
Nov 03, 2022 11:26 AM
Am I missing something? I’m testing with straight number values and still getting 0 with
MAX({Numbers 1},{Numbers 2})
Nov 03, 2022 11:36 AM
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})
Nov 03, 2022 01:09 PM
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'
)
Nov 03, 2022 01:51 PM
An alternate formula structure which doesn’t use a default date and instead returns a blank cell if neither date field is filled:
Nov 03, 2022 02:21 PM
Even better! Much simpler! Thank you, @Kamille_Parks! :grinning_face_with_big_eyes:
Nov 03, 2022 04:59 PM
I use a variation of this formula. But I often use the format specifier ‘x’, especially if there might be a time involved.
Nov 03, 2022 05:07 PM
Even simpler! And way more powerful! Love it!!