Skip to main content
Solved

Formula results are not recognized as dates

  • November 11, 2021
  • 3 replies
  • 43 views
1

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?

Best answer by Kamille_Parks11

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

3 replies

X
Forum|alt.badge.img+6
  • Xavier_G_Sabate
  • Participating Frequently
  • November 11, 2021

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

Kamille_Parks11
Forum|alt.badge.img+27

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]
)
kuovonne
1
1
  • 11191
  • Author
  • New Participant
  • November 12, 2021

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

Terms & ConditionsAccessibility statement