Issue with Nested If Formula - Value Returned Isn't a Date

[Heather_Parker]

Hi everyone -

I want to create a formula that will return a date value only if the value in a separate field {campaign duration (days)} is greater than 10. I also want the field to stay “clean” even if the bounding dates (Live and End) aren’t filled in. Please find below the formula I’ve created:

IF({Campaign Duration (Days)} < 10,"",IF(AND({Campaign Live Date},{Campaign Duration (Days)}),WORKDAY({Campaign Live Date},{Campaign Duration (Days)})))

Unfortunately the formula returns the following: 2021-08-19T00:00:000Z.

Can anyone tell me what I’m missing? The date itself is correct but I’m not even sure what the rest of the value is. I thought at first that this was a formatting issue but according to Airtable the result isn’t a number or a date so it can’t be reformatted.

[Kamille_Parks]

“2021-08-19T00:00:000Z” is a August 19, 2021 in “ISO” format. The characters after 19 denote the time of day and timezone.

What format are your {Campaign Live Date} and {Campaign Duration (Days)} fields?

[Heather_Parker]

Hi Kamille -

{Campaign Live Date} is a Date
{Campaign Duration (Days)} is a calculated field that is formatted as an Integer. The formula calculates the length of the campaign in days/2 so that I can easily see the campaign mid point - formula as follows in case it’s helpful:

DATETIME_DIFF({Campaign End Date},{Campaign Live Date},‘days’)/2

To note, {Campaign End Date} is also in the Date format.

Thanks so much!
Heather

[Kamille_Parks]

Try simplifying your formula down:

IF(
   AND({Campaign Live Date},{Campaign Duration (Days)}, {Campaign Duration (Days)} >= 10),
   WORKDAY({Campaign Live Date},{Campaign Duration (Days)})
)

[Justin_Barrett]

@Heather_Parker The reason that you’re seeing that ISO date format is because of the empty string that you included as the output from the IF() function if the campaign duration is less than 10 days. The documentation doesn’t talk about this, but the IF function will only output a single type of data. If there’s a mismatch between the data types returned by the second and third arguments, Airtable force-converts one to match the other. More often than not, using a string as output from one will force the other to become a string as well, which is what’s happening in your original formula. That ISO date format is what you get when brute-force converting a datetime to a string. @Kamille_Parks’ solution not only optimizes the logic, but it also solves the date-to-string problem by taking advantage of another undocumented feature of the IF() function: leave out the third argument, and the function automatically returns nothing, leaving the field empty.