Formula results are not recognized as dates

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]
)
2 Likes

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.