That’s odd.
I can’t duplicate the problem, presumably because of differences stemming from internationalization. My ‘friendly’ date format is different from yours; on the other hand, Airtable understands my formula evaluates to a date and offers me appropriate format choices. (Frankly, I would consider your results indicative of a bug, as any DATEADD()
function that doesn’t throw an error should return, by definition, a datetime value. You should probably bring this to Support’s attention.)
I suspect {Last Delivery}
being a rollup field also contributes to the weirdness. With rollup and lookup fields, Airtable correctly interprets the underlying field type — except for when it doesn’t. From your screenshot, it appears {Last Delivery}
is being retrieved as a datetime. (If you are given date options in {Last Delivery}
's formatting tab, it’s being seen as a datetime; if it doesn’t offer any control over formatting, it’s coming over as a test string.) The appearance of the ‘raw’ datetime value in {Next Delivery_2}
is characteristic of a datetime field being cast to text (for example, change {Last Delivery}
's aggregation function to something like ARRAYJOIN(values)
or simply values&''
) — which, as I mentioned earlier, shouldn’t be a possible result of DATEADD()
.
Appending an unnecessary BLANK()
‘else’ clause to your IF()
statement should have absolutely no effect on how Airtable interprets the result of that formula — if it does, it should probably be flagged as a bug. However, replacing an unnecessary empty string (''
) ‘else’ clause with an unnecessary BLANK()
— or, for that matter, simply eliminating an unnecessary empty string — very well could have an effect: In Airtable, if any single possible parsing of a formula results in a text value, then every possible result will be cast to text. Replacing that clause with a BLANK()
result, either explicit or implied, lets Airtable treat the calculation as a non-text value and offer context-specific formatting. Note again, though, it is removing the empty string, not adding the BLANK()
, that causes the change.
Finally, to get around to your actual question, you certainly can use DATETIME_FORMAT()
to present a datetime value in a particular style. There is an embarrassment of riches when it comes to content specifiers, with 'D MMMM YYYY'
the one corresponding to your preferred ‘friendly’ format.
To use, simply wrap DATETIME_FORMAT()
around any datetime value: fixed, calculated, or referenced. For example, a DATETIME_FORMAT()
-enabled version of your {Next delivery_2}
formula would be
IF(
{Last Delivery},
DATETIME_FORMAT(
DATEADD(
{Last Delivery},
1,
'month'
),
'D MMMM YYYY'
)
)