Having trouble with dates in conditional rollup field

[Victoria_Newbor]

Hi there!

I’m working in two tables: Contacts and Interactions. The latter keeps records of all emails to and from Contacts (linked), including time/date and a single select field to indicate whether the email was inbound or outbound.

I also have simple formula fields – IF(Type=‘To Contact’, Date, ‘0’) and IF(Type=‘From Contact’, Date, ‘0’) – because I’m trying to create respective rollup fields in the Contacts table to show the date of the most recent inbound email and the most recent outbound email.

I can’t get my rollup to display any dates. MAX(values) displays zeros. Using ARRAYJOIN and then using DATETIME_PARSE(MAX({arrayjoinfield})) displays 1/1/1970.

Is there anything I can add to the Interactions formulas so that the dates are read as values? Or is there a different function you can recommend for the rollups? I’m really scratching my head here.

[Justin_Barrett · Accepted solution]

Welcome to the community, @Victoria_Newborn! :grinning_face_with_big_eyes: I have a hunch that you’re encountering this issue because of the “0” string you’re using as the fallback value in those formulas. Because that fallback value is a string, Airtable assumes that the output in that field should always be a string, so it turns the date into a string in the most simple way it knows. That’s why you can’t use MAX() to find the latest date: because the date is no longer a date, but a string.

To fix this, just drop the “0” string completely, and Airtable will leave the field empty if the condition fails. Not only that, but it will then use the only other option—the date from the {Date} field—to determine that the field formatting should remain as a datetime object, and not be converted into any other format. Your {To Contact Condition} formula would become:

IF(Type='To Contact', Date)

And the formula for {From Contact Condition} would be

IF(Type='From Contact', Date)