Jun 26, 2018 10:23 PM
I have two rollups, one for “latest morning absences” and another for “latest evening absences”. What formula do I use to return the latest of the two?
Jun 26, 2018 11:52 PM
What is the field type of “latest morning absences” and “latest evening absences”?
Jun 27, 2018 03:42 AM
MAX()
is the function you need.
Jun 27, 2018 03:46 AM
As, @Elias_Gomez_Sainz said, there’s the MAX(VALUES)
function :winking_face:
Just in case, this might interest you
Jun 27, 2018 07:16 AM
Hello, I was able to create a Formula field and use:
IF({Latest Morning Absence} < {Latest Evening Absence}, {Latest Evening Absence}, {Latest Morning Absence})
to return the most recent of the two date rollups :slightly_smiling_face: I tried MAX(value1, value2) but it did not work for me. Maybe I had the syntax incorrect?
Jun 27, 2018 07:29 AM
A Rollup gets several values and do some function with all of them, so we have assumed you want the most recent in every of those 2 fields. BTW, how do you get that Latest Morning Absence
?
If you just want to compare 2 dates (however they are generated/entered), you can use your formula with operator, as well the IS_AFTER()
and IS_BEFORE()
functions.
Jun 27, 2018 07:45 AM
Hi Elias,
Latest Morning Absence
is a rollup field, and was one of the options in the drop down menu when I started working out the formula. Sorry - I am fairly new to Airtable ways and means, does that answer your question?
Thank you for the suggestions!
Jun 27, 2018 09:39 AM
No problem! Just clarifying that what you wanted is easier than we thinked of :grinning_face_with_sweat: But, in those Rollup fields, how do you get that Latest Item? I think MAX()
is the only way.
Of course to check if a date is most recent than other is that easy :winking_face:
Jun 27, 2018 10:38 AM
Ah, gotcha, yes the two rollup fields are obtained by MAX(values)
:slightly_smiling_face:
Jun 27, 2018 02:16 PM
Assuming your rollups fields are formatted as « simple dates » (no time field included), I did try your first formula, without the empty spaces, and it seems to work… :woman_shrugging:
({Form 1}
in the screenshot below)
IF({Latest Morning Absence}<{Latest Evening Absence},{Latest Evening Absence},{Latest Morning Absence})
Following your first formula I tried this one too ({Form 2}
in the screenshot below)
IF(
IS_BEFORE(
{Latest Morning Absence},
{Latest Evening Absence}
),
{Latest Evening Absence},
{Latest Morning Absence}
)
The results of both of these 2 formulas are formatted as dates (local) without a time field as the 2 rollup fields are.
Edit : 2nd screenshot where {Latest Morning Absence} < {Latest Evening Absence}
for one of the Test