Formula Field Result Type is not Date

Dan_Pinsky · ‎Jun 22, 2022

I have an needlessly complicated formula field that, despite my best efforts, still does not produce a result type as a date despite making every possible result return a date value.

Here is my formula:

IF({Date Sent to Captioning}=BLANK(),BLANK(),
  SWITCH(
  {Captioning Speed Requested},
  '1 day',
DATETIME_FORMAT(DATEADD({Date Sent to Captioning}, 1, 'days'), 'M/DD/YYYY')
, '2 day',
DATETIME_FORMAT(DATEADD({Date Sent to Captioning}, 2, 'days'), 'M/DD/YYYY')
, '4 day',
DATETIME_FORMAT(DATEADD({Date Sent to Captioning}, 4, 'days'), 'M/DD/YYYY'),
BLANK()
)
)

Originally this was just a simple SWITCH formula to add a number of days to a date column based on three possible options from a single select column called “Captioning Speed Requested” with options for “1 day,” “2 day” and “4 day.” I want it to add 1, 2 and 4 to the date entered to have it automatically give me an ETA based on number of days selected. I had the remainder to return a BLANK() value to prevent an error from appearing when no value was selected for the single select column, and I later embedded this into an IF formula to return another BLANK() value to prevent a similar error from appearing when no value was entered in the date column. I also found that I had to insert a DATETIME_FORMAT formula into each result, as otherwise I was getting results like “2022-06-22T00:00:00.000Z.”

I eventually got the result I was looking for, but for some reason, the column was not giving me a result type of date, so I couldn’t use it as a date in any views or automations that reference dates. According to this article (https://support.airtable.com/hc/en-us/articles/360034619694-Common-formula-errors-and-how-to-fix-the...) on common formula errors, it seems that if it’s at all possible for any of my formula results to return a non-date entry, the result type cannot be recognized as a date, so I tried replacing the BLANK() values with a generic {Today} column formula that returned a DATE() value. In the past, I’ve encountered a similar issue when the date columns referenced did not have the time and time zone options selected, so I tried enabling those options in the date and {Today} column as well.

However, regardless of any of these variations, no version is returning a result type as a date. I’m able to save each time, so I know I’m configuring my formulas correctly. But in order to reference this column as a date in views or automation, it must be a result type of date, and I don’t know how to make this happen. Please help.

JonathanBowen · ‎Jun 22, 2022

Hi @Dan_Pinsky - yes, I think oyu can simplify this, removing the BLANK():

IF({Date Sent to Captioning}, 
  SWITCH(
    {Captioning Speed Requested},
    '1 day', DATEADD({Date Sent to Captioning}, 1, 'days'),
    '2 day', DATEADD({Date Sent to Captioning}, 2, 'days'),
    '4 day', DATEADD({Date Sent to Captioning}, 4, 'days')
  )
)

You could also consider making the “Speed Requested” field a number:

which simplifies the formula some more:

IF(
  {Date Sent to Captioning}, 
  DATEADD({Date Sent to Captioning}, {Captioning Speed Requested (days)}, 'days')

)

Dan_Pinsky · ‎Jun 22, 2022

That worked! I didn’t know the remainder portion of IF and SWITCH formulas could simply be dropped completely! That makes it much more simple!

kuovonne · ‎Jun 23, 2022

Or leave it a single select and use VALUE()

IF(
  {Date Sent to Captioning}, 
  DATEADD(
    {Date Sent to Captioning}, 
    VALUE({Captioning Speed Requested (days)}), 
    'days'
  )
)