Help

Re: How do I return the most recent of two dates?

5580 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Patrick_Kenned1
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

22 Replies 22
Andrew_Johnson1
8 - Airtable Astronomer
8 - Airtable Astronomer

What is the field type of “latest morning absences” and “latest evening absences”?

MAX() is the function you need.

Ptt_Pch
8 - Airtable Astronomer
8 - Airtable Astronomer

As, @Elias_Gomez_Sainz said, there’s the MAX(VALUES) function :winking_face:

Just in case, this might interest you

Patrick_Kenned1
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

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.

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!

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:

Patrick_Kenned1
5 - Automation Enthusiast
5 - Automation Enthusiast

Ah, gotcha, yes the two rollup fields are obtained by MAX(values) :slightly_smiling_face:

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.

0F4F8A55-2954-4314-ACC5-559D2D902E57.jpeg

Edit : 2nd screenshot where {Latest Morning Absence} < {Latest Evening Absence} for one of the Test

91EE2C5E-479D-4831-966B-840FFF233E5F.jpeg