What is wrong with my formula?

Platform discussions Special workflows & use cases
, ,
1

I’ve been working on this formula to calculate a date based on a status column. I’ve been spinning my wheels for the better part of this afternoon since it’s a huge part of our use case that I want to transfer out of excel.

FORMAT_DATE

(

SWITCH(

{Status},

"OFA",

IF(

  TODAY() > ADD_DAYS({Submitted}, 21), ADD_DAYS(TODAY(), {Eq. Lead Time (Early)}),

  ADD_DAYS({Submitted}, 7\*(3+{Eq. Lead Time (Early)})) 

),

"Release  Requested", 

IF( 

  TODAY() > ADD_DAYS({Release Requested Date}, 14),

  ADD_DAYS(TODAY(), 7\*{Eq. Lead Time (Early)}),

  ADD_DAYS({Release Requested Date}, 7\*(2+{Eq. Lead Time (Early)}))

)

)

)

What am I missing here? I have tried a similar thing in a different column which works just fine:

FORMAT_DATE(

SWITCH(

{Status}, 

"OFA", 

ADD_DAYS({Submitted},21), 

"Release Requested", 

ADD_DAYS({Release Requested Date},14)

)

)

Please save my sanity!

2

Hey @SamG1

The first issue I see is the “\” in the formula:
ADD_DAYS({Submitted}, 7\*(3+{Eq. Lead Time (Early)}))

Is that actually in your formula text?

Also the “(” after FORMAT_DATE must be directly following, without a space or carriage return. Like “FORMAT_DATE(”

Jim - The Monday Man

1 Like
3

Hey Jim,

No the “/” was not in the equation, but moving the “(“ to right after the FORMAT_DATE solved my issue, thanks so much!

1 Like
4

Your formula issue comes down to Airtable choking on mismatched parentheses the SWITCH logic is fine, but one IF block isn’t actually closed, and you also had an extra ) in the OFA branch. The wrong part is: ADD_DAYS({Submitted}, 7*(3+{Eq. Lead Time (Early)})) that’s too many closing brackets and the correct version is: ADD_DAYS({Submitted}, 7 * (3 + {Eq. Lead Time (Early)})) plus the full IF needs to close properly like : IF( TODAY() > ADD_DAYS({Submitted}, 21), ADD_DAYS(TODAY(),{Eq. Lead Time (Early)}), ADD_DAYS({Submitted}, 7 * (3 + {Eq. Lead Time (Early)})) ). Fixing those parentheses cleans everything up and Airtable will accept the formula without errors