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

3710 3
Showing results for 
Search instead for 
Did you mean: 
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
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.

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

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:

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)

			{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


6 - Interface Innovator
6 - Interface Innovator

How do I use MAX() with two regular date values? I tried MAX({Start Date},{End Date}) but it always returns 0.

I don’t believe MAX() is intended for use with dates. That’s why the IS_BEFORE() and IS_AFTER() functions were mentioned. The comment immediately above yours contains a full formula example.

It works with dates, for instance in a Rollup field:


Yes, it works in rollup fields, but the question from @Dan_Weaver above was about comparing two regular date values, specifically pointing to an issue using MAX(). In formulas, MAX() doesn’t work with dates. Only numerical values.

5 - Automation Enthusiast
5 - Automation Enthusiast

I wish there was a MAX_DATE function. This monstrosity is what I used in its place:

DATETIME_FORMAT(DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT(IF({Publish Date}, {Publish Date}, '0'), 'X')), VALUE(DATETIME_FORMAT(IF({Due Date}, {Due Date}, '0'), 'X')), VALUE(DATETIME_FORMAT(IF({Completed Date}, {Completed Date}, '0'), 'X'))), 'X'), 'YYYY-MM-DD')

You can see I convert all the dates to a number using VALUE(DATETIME_FORMAT(IF. The IF is needed because you get an error for empty cells with DATETIME_FORMAT. The VALUE is needed because MAX doesn’t work with strings. Then at the end I format it back to a date.

6 - Interface Innovator
6 - Interface Innovator

If I need to find the max or min values of a date field I do the following:

  1. I convert, in the original table, them to the format ‘YYYYDDDD’ usibg DATETIME_FORMAT(date,‘YYYYDDDD’)+0 , so I transform it to an integer field (int_date).
  2. On the receiving table I use a rollup with either a max or min values to recover the max/min ‘INTEGER’ field
  3. on the receiving table I convert back the ‘INTEGER’ field to a data field using the DATETIME_PARSE(int_date , ‘YYYYDDDD’,‘L’)
  4. done …
7 - App Architect
7 - App Architect

What would be a way to return last date of the call that already happened.
One of the dates is in the past and the other in the future.
So a rollup field won’t work

Use a conditional rollup when calculating the maximum date. Set the condition to include only dates that are on or before today.

How would I miss this!
Thank you @kuovonne.

Your approach is very smart but the time formatting is wrong. I also used INT instead of hacking +0 to parse it to an integer. Other than that, great… too bad AirTable does not provide a better way to do this. It’s definitely not a friendly way to do it, in particular if you have 4 or 5 dates.