The MAX() function will work for that, even though the formula reference page only states that it works with numbers.
The MAX() function will work for that, even though the formula reference page only states that it works with numbers.
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})
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})
Am I missing something? I’m testing with straight number values and still getting 0 with
MAX({Numbers 1},{Numbers 2})
Am I missing something? I’m testing with straight number values and still getting 0 with
MAX({Numbers 1},{Numbers 2})
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})
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'
)
An alternate formula structure which doesn’t use a default date and instead returns a blank cell if neither date field is filled:
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:
An alternate formula structure which doesn’t use a default date and instead returns a blank cell if neither date field is filled:
I use a variation of this formula. But I often use the format specifier ‘x’, especially if there might be a time involved.
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'
)
)
An alternate formula structure which doesn’t use a default date and instead returns a blank cell if neither date field is filled:
ahhh thank you so much @Kamille_Parks @kuovonne @ScottWorld !!
The MAX() function will work for that, even though the formula reference page only states that it works with numbers.
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.
