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

“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?

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

Try simplifying your formula down:

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

@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.