Rollup & Max & Dates & Formulas & Formatting = Loop / Freeze

[Tuur]

MAX(values) doesn’t work properly in the scenario where you rollup a date field which uses formatting. It’s no longer recognised as a date and can cause problems.

It also looks like old field level formatting is stored when a datatype changes. I used an existing field that was formatted for currency and changed the formula to simply show a date field. My rollup in the parent record caused a major problem later on although the child field appeared as it should: I was locked out the parent table as it got stuck on the ‘Loading’ screen.

To re-enable access to that table I had to change the formula in the child table.

FYI. :slightly_smiling_face:

[Arlo_Haskell]

I think I’m having a similar issue. I’m trying to use MAX() to return the most recent date from two rollup fields, but it doesn’t work as expected.

The formula MAX({Rollup Date 1}, {Rollup Date 2}) returns 0

while the formula ‘DATETIME_PARSE(MAX({Rollup Date 1}, {Rollup Date 2}))’ returns 1/1/1970

[Gareth_Pronovos]

Hey folks - I came here hoping to find a solution to this bug that I’m also experiencing. Unfortunately, it looks like one does not yet exist, so I had to build a bit of a workaround. Check it out here - while this certainly isn’t ideal, it solves the problem of finding the latest (or ‘max’) date.

Hope this helps!

[W_Vann_Hall]

Here’s another way to find the latest of three dates — still jumps through too many hoops, but at least has the good grace to do so within a single formula. :winking_face:

Assumes three date fields, {Date1}, {Date2}, and {Date3}. Returns a date, with formatting specified in the field configuration.

DATETIME_PARSE(
    MAX(
        VALUE(
            DATETIME_FORMAT(Date1,'x')
            ),
        VALUE(
            DATETIME_FORMAT(Date2,'x')
            ),
        VALUE(
            DATETIME_FORMAT(Date3,'x')
            )
        ),
    'x'
    )

Oddly enough, this also seems to work with rollup fields, although I can’t for the life of me understand why. (Even though Airtable tells me the the rolled-up date field — which it displays in ISO-8601 interchange format; e.g., 2018-11-13T00:00:00.000Z — is not recognized as a date, seemingly it is a valid input to DATETIME_FORMAT().)

BTW, the 'x' format specifier causes DATETIME_FORMAT() and DATETIME_PARSE() either to return or to expect a date expressed as a Unix timestamp. I increasingly make use of such in Airtable, as doing so avoids a myriad potential pitfalls owing to mismatched or region-specific formats.

[Tuur]

Tuur:

MAX(values) doesn’t work properly…

@Danielle - Same as former move - not working as expected = buggy.

[W_Vann_Hall]

@Tuur – I can’t duplicate this. Can you step me through the process?

[Tuur]

@W_Vann_Hall I’ll have a look. Maybe it’s been changed, the original post is from 2016… :hourglass_flowing_sand:

[W_Vann_Hall]

I was mainly having a problem coming up with a formula that could be formatted as either currency or a date. :winking_face:

[Janet_Dunlap]

Thank you for the formula.
How can you modify the formula to show the max() even if one of the date fields is blank (ie. no value in the field)?
If one of the date fields is blank, I get #ERROR.

[Tuur]

You could put a dummy date of e.g. ‘1900’ in there if one is empty if that works for you? Be aware that it will show that date when there are no dates at all though. :slightly_smiling_face:

Keep the formula as is, but replace ‘Date1’ (and the others obviously) with something like:

IF({Date1}, {Date1}, DATETIME_PARSE('1900-01-01'))