Formula not recognised as date

[jwag]

Morning!

I'm having trouble getting Airtable to recognise the output of this formula as a date. It is basically saying "if it's in this city/country, you need to begin the project x,y or z number of months before the required by date".

The last "otherwise" was "" but I've changed it to -999 so the none of the formula would spit out a string value, but that still doesn't seem to have worked. The {Client Required By Date} field is a date field in the same table and is date only (no time), so that shouldn't be an issue, I don't think. Formula below:

DATETIME_FORMAT(
  IF(
    OR(
      {Town/City} = "Glasgow",
      {Town/City} = "Edinburgh",
      {Town/City} = "London",
      {Town/City} = "Liverpool"
    ),
    DATEADD({Client Required By Date}, -7, "months"),
    IF(
      FIND({Country}, "United Kingdom,Spain,Portugal,Republic of Ireland,Poland"),
      IF(
        AND(
          {Country} = "United Kingdom",
          {Bed Spaces / Units (all)}> 500
        ),
        DATEADD({Client Required By Date}, -7, "months"),
        DATEADD({Client Required By Date}, -6, "months")
      ),
      IF(
        FIND({Country},"Austria,France,Netherlands,Sweden,Finland,Denmark,Italy"),
        DATEADD({Client Required By Date}, -8, "months"),
        IF(
          FIND({Country},"Germany"),
          DATEADD({Client Required By Date}, -12, "months"),
          DATEADD({Client Required By Date}, -999, "years")
        )
      )
    )
  ),
  'DD/MM/YYYY'
)

Does anyone have any ideas how I can solve this?

Many thanks!

[Sho · Accepted solution]

Hello @jwag ,

Datetime_format is the cause.

Datetime_format returns a string, not a date type.
If the last output is a date type, you can specify the date format in the "Formatting" section of the Formula field settings.

"DATEADD({Client Required By Date}, -999, "years")"
In this case, it would be better to use BLANK() or nothing to output.

[jwag]

Hi @Sho,

That's excellent, that has worked. Thank you!