Help

Re: [SOLVED] DATETIME_FORMAT in Rollup Field?

2562 8
cancel
Showing results for 
Search instead for 
Did you mean: 
Shayne_Bundy
6 - Interface Innovator
6 - Interface Innovator

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!

10 Replies 10
Arlo_Haskell
7 - App Architect
7 - App Architect

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

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

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… .

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.

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.

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!

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

Chantelle_Nash
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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

As the aggregation formula for the Roll-Up field: ARRAYJOIN(values, “\n”)