Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Formula results are not recognized as dates

Topic Labels: Formulas
Solved
Jump to Solution
460 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

1 Solution

Accepted Solutions

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

See Solution in Thread

3 Replies 3

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-FO...

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

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