Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Formula not recognised as date

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
1399 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!