How do I add DATETIME_FORMAT to this formula

[Dirk_Jan_de_Wit]

Hi awesome community.

I want to create a new field that calculates the previous delivery date + 1 month.
So I used this:
DATEADD({Last Delivery},1,‘month’)

But the problem was that it gave me errors when the date was blank. So I tried using the IF formula.
IF ({Last Delivery}, DATEADD({Last Delivery},1,‘month’))

But now it doesn’t come in a friendly format. I figure I have to use the DATETIME_FORMAT but I’m not sure how. I would like to see for example 6 April 2019

Thanks for your help!

[DrShrink]

in the formula filed setting
there is another tab for choosing format of date

[Y_K]

Add a BLANK() to your IF function:
IF ({Last Delivery}, DATEADD({Last Delivery},1,‘month’),BLANK())

Then choose the Date Format in the second tab:

[W_Vann_Hall]

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'
        )
    )

[Dirk_Jan_de_Wit]

Thanks Y_K, this solved it!

[Dirk_Jan_de_Wit]

Thanks W_Vann_Hall, adding the BLANK() to the formula solved it!

[W_Vann_Hall]

adding the BLANK() to the formula solved it!

As I said,

if it does, it should probably be flagged as a bug.

If adding the BLANK() solved it, that means DATEADD() in the original formula was somehow evaluating as a text string — which, given the syntax of your IF() statement, should have been impossible. As I can’t reproduce the problem, I have to assume the bug surfaces only in certain locales…

---

Note: After testing with my browser set for various locales, I still cannot duplicate your original issue. The only way I can match your result for {Last Delivery_2} is by adding an explicit ‘else’ branch that evaluates to an empty string (’''’). If the formula in use for {Last Delivery_2} did contain an empty string, then we know what the problem was. If it didn’t contain one, then there’s still something strange afoot.

[Samantha_Vale]

Thankyou, adding the BLANK() helped me out too!