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

MAX() is the function you need.

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

Just in case, this might interest you

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 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:

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

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

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.

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.

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 …

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.

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

I also need to return the latest date (of many dates, and most are in the future.). Any reason why this doesn’t work? DATETIME_PARSE(MAX(INT(DATETIME_FORMAT({Ins CR}, ‘YYYYMMDD’)),INT(DATETIME_FORMAT({App CR}, ‘YYYYMMDD’))),INT(DATETIME_FORMAT({Loan CR}, ‘YYYYMMDD’)))),INT(DATETIME_FORMAT({COP CR}, ‘YYYYMMDD’))))),“YYYYMMDD”)

@Jordan_Scott1 - I feel this is one of those missing links that Airtable Devs could add to their to-do list. Max({date_a},{date_b}) returns a 0 instead of the largest date. I went to use this method just now and was surprised to find it fail, and ended up using ugly If statements as a workaround.

@Valerie_Miles - I feel your pain! Make sure you also inform Airtable support.

Jup, should be fixed by the devs. would be a very nice feature.