DateAdd Returning String of Numbers

[S_Pacheco-Willi]

Hi there!

I’m trying to use the IF and DATEADD formulas to calculate a Project Start Date based on whether or not a Launch Date is entered for a record. If there is no Launch Date entered, the record field should be left blank.

Here is the formula:
IF({LAUNCH DATE} = BLANK(), “”, DATEADD({LAUNCH DATE}, -16,‘week’))

What I am getting is a date string (2021-10-18T00:00:00.000Z) instead of the Friendly Date format.

Help please! :slightly_smiling_face:

[Ben_Young1]

Hello @S_Pacheco-Williams!

Here you go!

IF(
    {LAUNCH DATE},
    DATETIME_FORMAT(
        DATEADD(
            {LAUNCH DATE}, 16, 'weeks'
        ), 'l'
    ),
    ''
)

Here, you’ll want to use the DATETIME_FORMAT function to format the raw date/time string that it was spitting out at you.

Here’s what it looks like from my end.

Please let me know if you have any questions about this!

[S_Pacheco-Willi]

Thank you Ben! Is there an additional formula we can add to return the date in the format “Wednesday, February 9, 2022” for example?

[Justin_Barrett · Accepted solution]

There are two important things to remember when using Airtable’s IF() function:

1. If you include an empty string as one of the arguments, Airtable will force the other output to be a string as well. That’s why the date looks so odd in your original example, @S_Pacheco-Williams
2. The final argument in the IF() function is optional. If you omit it, Airtable will return nothing. In that light, it’s almost never necessary to include an empty string (which introduces the problem mentioned in #1) or the BLANK() function.

Also know that you can test virtually any field for being full/empty without using the BLANK() function. Just put the field reference itself. If the field has any data, Airtable treats it as True (or truthy in coding circles); if it’s empty, it’s equivalent to False (or falsy).

With all that in mind, your original formula can be rewritten like this:

IF({LAUNCH DATE}, DATEADD({LAUNCH DATE}, -16, 'week'))

To format it as you requested:

IF({LAUNCH DATE}, DATETIME_FORMAT(DATEADD({LAUNCH DATE}, -16, 'week'), "dddd, LL"))

You might also run into slight discrepancies because dates are stored internally relative to GMT. To ensure that the date is correct for your local timezone, use this variant (spread out for easier reading; you can safely copy and paste this into the formula field and it will still work):

IF(
    {LAUNCH DATE},
    DATETIME_FORMAT(
        SET_TIMEZONE(
            DATEADD({LAUNCH DATE}, -16, 'week'),
            "timezone_specifier"
        ), "dddd, LL"
    )
)

Replace “timezone_specifier” with the appropriate string for your timezone:

Airtable Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the format sp...

[S_Pacheco-Willi]

Thank you both, appreciate your help!!!

[kbramlett]

Hello! This has been very helpful for me, however, I was wondering if there is a way to put more than one date into one cell. For example, I'm looking to have a column that displays three "deadlines" in relation to an event date. If the event date is March 5, there will be three dates I'd like listed: 4 weeks prior, 2 weeks prior, and week of. 

I'm not very privy to where additional fields can be added in the formula, or if it's even possible. Thanks in advance!