Help

Formula not recognised as date

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
623 2
cancel
Showing results for 
Search instead for 
Did you mean: 
jwag
6 - Interface Innovator
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

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.

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

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
6 - Interface Innovator
6 - Interface Innovator

Hi @Sho,

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