Help

Formula results are not recognized as dates

Topic Labels: Formulas
Solved
Jump to Solution
859 3
cancel
Showing results for 
Search instead for 
Did you mean: 
11191
4 - Data Explorer
4 - Data Explorer

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
Kamille_Parks
16 - Uranus
16 - Uranus

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
Xavier_G_Sabate
6 - Interface Innovator
6 - Interface Innovator

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

Kamille_Parks
16 - Uranus
16 - Uranus

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