How do I return the most recent of two dates?

[Patrick_Kenned1]

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?

[Andrew_Johnson1]

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

[Elias_Gomez_Sai]

MAX() is the function you need.

[Ptt_Pch]

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

Just in case, this might interest you

Most Recent Date from another table Ask the community

Here’s how I would go about constructing a “Date of last contact” field for a table of clients linked to a table of client interactions. In the table of client interactions, I have a linked record field for the name of the client, a date field for the date of each interaction, and a primary field which uses a formula to name each record. [image] In the client table, create a rollup field which uses the linked record field connected to the client interactions table. Select the dates field (co…

[Patrick_Kenned1]

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?

[Elias_Gomez_Sai]

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.

[Patrick_Kenned1]

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!

[Elias_Gomez_Sai]

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]

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

[Ptt_Pch]

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

[Dan_Weaver]

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

[Justin_Barrett]

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.

[Elias_Gomez_Sai]

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

[Justin_Barrett]

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.

[Yuval_Greenfiel]

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.

[Benito_Abraham]

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 …

[Mariusz_S]

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

[kuovonne]

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

[Mariusz_S]

How would I miss this!
Thank you @kuovonne.

[Hernan_Haro]

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.

DATETIME_PARSE(MAX(INT(DATETIME_FORMAT({DATE1}, ‘YYYYMMDD’)),INT(DATETIME_FORMAT({DATE2}, ‘YYYYMMDD’))),“YYYYMMDD”)