# DateAdd Returning String of Numbers

Topic Labels: Formulas
Solved
1836 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
18 - Pluto

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(
"timezone_specifier"
), "dddd, LL"
)
)
``````

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

5 Replies 5
11 - Venus

Hello @S_Pacheco-Williams!

Here you go!

``````IF(
{LAUNCH DATE},
DATETIME_FORMAT(
{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.

5 - Automation Enthusiast

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

18 - Pluto

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(
"timezone_specifier"
), "dddd, LL"
)
)
``````

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

5 - Automation Enthusiast

Thank you both, appreciate your help!!!

4 - Data Explorer

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!