[SOLVED] DATETIME_FORMAT in Rollup Field?

[Shayne_Bundy]

I cannot get DATETIME_FORMAT to work in a Rollup Field. There are multiple dates present in the Rollup Field, and each displays correctly when NOT using the DATETIME_FORMAT
I am attempting: DATETIME_FORMAT(ARRAYJOIN(values, “\n”), “M/DD/YYYY”)

But that results in an #ERROR! when there are multiple dates present in the Rollup. It works correctly when there is only one date present in the Rollup Field.

I’ve found a couple topics on this same issue, but no resolutions. Thank you!

[Arlo_Haskell]

Your example has double quotation marks around the datetime format specifier, but Airtable calls for single-quotation marks. Could that be the issue? See here: https://support.airtable.com/hc/en-us/articles/216141218-Supported-format-specifiers-for-DATETIME-FO...

[Shayne_Bundy]

Shayne_Bundy:

DATETIME_FORMAT(ARRAYJOIN(values, “\n”), “M/DD/YYYY”)

Single quotation marks still result in the #ERROR! in fields with more than one date.

[W_Vann_Hall]

Unfortunately, there’s no way to apply DATETIME_FORMAT() to multiple date strings returned by ARRAYJOIN() en masse; instead, you’ll either need to format the dates before they are rolled up or you’ll need to break out each date and format it separately. The former is easiest, assuming it works with the rest of your application; the latter can be anywhere from annoying to essentially impossible, depending upon the largest number of dates you anticipate having to support… .

[Shayne_Bundy]

I have no problem formatting the dates before they are rolled up, but I am not sure how to do that…since I thought I already did it, LOL.
The Rollup field in question is a roll-up of a Date field. The original Date field is formatted to M/D/YYYY (called “US” in the Date formatting options). If I remove the DATETIME_FORMAT, a single, rolled-up date displays correctly (M/D/YYYY). But if the Rollup field has multiple dates, it displays them as YYYY-MM-DDHH:MM:SS.000Z. And I can’t re-format it.

[W_Vann_Hall]

Shayne_Bundy:

The original Date field is formatted to M/D/YYYY (called “US” in the Date formatting options).

Unfortunately, the ‘formatting’ tab controls only the appearance of the date when displayed in that field; the date is still treated as a Unix date value (or a calculation resulting in a Unix date value, depending).

You’ll need to define another formula field in the table, calling it, oh, {Cooked Date}. (I often use ‘cooked’ and ‘raw’ to help me tell fields apart; feel free to call the field whatever you wish. It can be kept hidden to reduce clutter.) Configure the formula to

DATETIME_FORMAT({Raw Date},'M/D/YYYY')

Now you can roll up {Cooked Date} with '\n' as your separator field for ARRAYJOIN(), and it will work out as you expected.

[Shayne_Bundy]

Now you can roll up {Cooked Date} with ‘\n’ as your separator field for ARRAYJOIN(), and it will work out as you expected.

…This did it. This multi-step process is exactly what I was trying to figure out!!
I was secretly hoping that you would chime in when I created this thread; I have read many “Ask the community” posts, and you’re obviously an Airtable wizard. I am working on a wardrobe documentation/outfit creation database, and took a look at your theater-wardrobe example for some ideas when I first began.
So, I am not surprised that you knew the answer, but I am very pleased!

[W_Vann_Hall]

Aw, shucks — you’re too kind. (It’s amazing what a little work avoidance can bring about! :winking_face: )

[Chantelle_Nash]

Could something similar to this work for the date field showing in automated e-mail notifications with this long string? I hate how it includes the big long thing instead of just a clean date/time.

[Betsy_DeWitt]

Shayne_Bundy:

Now you can roll up {Cooked Date} with ‘\n’ as your separator field for ARRAYJOIN(), and it will work out as you expected.

…This did it. This multi-step process is exactly what I was trying to figure out!!
I was secretly hoping that you would chime in when I created this thread; I have read many “Ask the community” posts, and you’re obviously an Airtable wizard. I am working on a wardrobe documentation/outfit creation database, and took a look at your theater-wardrobe example for some ideas when I first began.
So, I am not surprised that you knew the answer, but I am very pleased!

Can you share the final code for this? I’m having trouble working in the ARRAYJOIN() bit.