Skip to main content

I came up with the following formula.


IF({Registration date}=BLANK(),

‘’,

SWITCH({PLAN},

‘A’,

SWITCH({TYPE},

‘1’,DATEADD({Registration date},30, “days”),

‘2’,DATEADD({Registration date},30, “days”),

‘3’,DATEADD({Registration date},30, “days”)

),

‘B’,

SWITCH({TYPE},

‘2’,DATEADD({Registration date},30, “days”)),

‘3’,DATEADD({Registration date},30, “days”),

‘5’,DATEADD({Registration date},30, “days”)

) ) )


Depending on the PLAN and TYPE, we want to return the value of {Registration date} plus the number of days.


And if {Registration date} is blank, I want the value of the formula to be blank as well (to avoid #ERROR).


However, the returned value is not recognized as a date and is displayed as follows


2021-12-17t00:00:0.000z


If I remove the IF statement at the beginning, it is recognized as a date format.


Is there any way to resolve this?

Hi,


dealing with dates is always tricky… try to format the outcome by using DATETIME_FORMAT(, )

I think that the final result is a Date type. You can get more info here: https://support.airtable.com/hc/en-us/articles/216141218-Supported-format-specifiers-for-DATETIME-FORMAT


good luck

Your IF statement essentially says: If {Registration Date} is empty then return an empty string, otherwise return a date. When any possible output of a Formula is a string, all the results will be a string. So restructure the IF to do one of the following:


IF(

{Registration date}=BLANK(),

BLANK(),

[your nested SWITCH statements]

)

or


IF(

{Registration date},

[your nested SWITCH statements]

)

Your IF statement essentially says: If {Registration Date} is empty then return an empty string, otherwise return a date. When any possible output of a Formula is a string, all the results will be a string. So restructure the IF to do one of the following:


IF(

{Registration date}=BLANK(),

BLANK(),

[your nested SWITCH statements]

)

or


IF(

{Registration date},

[your nested SWITCH statements]

)

I got exactly what I wanted! Thank you very much! from japan

Reply


Terms & Conditions