Skip to main content
Solved

Any reason why this SWITCH() Formula is not returning a number?

  • June 9, 2020
  • 4 replies
  • 56 views
A
Forum|alt.badge.img+3

I am attempting to calculate asset depreciation using Airtable. I am calculating using the following Fields:

Purchase Date: Date field
Unrecovered Basis 1: A formula which is returning a currency value
1 Year (from Depreciation Conventions): This is a lookup to another table which has the 1 year depreciation amount. This is a numerical value (decimal)

I have reduced the formula I am attempting to use down to it’s simplest form. There will be more switch states, but it doesn’t even work with the 1, so I didn’t want to confuse matters by adding more. The formula returns a result just fine, but I am unable to format it as a number (currency) and thus, am unable to use the value in future formulas.

The Formula is this:

SWITCH(DATETIME_FORMAT({Purchase Date}, ‘YYYY’) - 2015,1,{Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)},BLANK())

When attempting to format the field, it states “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.”

If I put the exact same formula outside the switch statement, it works. Does switch not return numbers by default? What can I do to make it do so?

Thanks!

Best answer by kuovonne

Thanks for trying to help! I really appreciate it. I have changed the formula to what you suggested and am getting the same result.

here is a link to a screenshot of the relevant fields.

The formula I am using is now: SWITCH(YEAR({Purchase Date}) - 2015,1,{Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)},BLANK())

As you can see, the 2016 Depreciation field is not formatting as a number, and when I try to, it says that the result type is not a number.

If I make the formula just: {Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)} it works, and is able to be formatted as a number. But obviously, I need to be able to set up the Switch conditions.?

I’m starting to wonder if Switch returns strings instead of numbers by default, and there’s nothing we can do about it. I even attempted to wrap the entire thing in a VALUE(), and that didn’t help.

Once again, thanks for trying to help!!


Thank you for the screen capture.

A formula field can be formatted as a number only if all possible return values are numbers.

For your SWITCH statement, a possible return value is BLANK(). You can simply remove this last part of the formula, as it isn’t necessary. Then you should be able to format the field as a number.

SWITCH( YEAR({Purchase Date}) - 2015,
  1, {Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)}
)

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

4 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • June 9, 2020

Welcome to the Airtable community!

You don’t have the right syntax for the initial expression of the switch statement.

DATETIME_FORMAT({Purchase Date}, ‘YYYY’) - 2015

The DATETIME_FORMAT function returns a string, and it looks like you are trying to subtract a year from it, which doesn’t work because you can’t subtract a number from a string. Try this formula, which gets the year as a number:

YEAR({Purchase Date}) - 2015

Here are some support articles the Switch function and the Formula field reference.

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

A
Forum|alt.badge.img+3
  • Alan_Reid
  • Author
  • Participating Frequently
  • June 9, 2020

Welcome to the Airtable community!

You don’t have the right syntax for the initial expression of the switch statement.

DATETIME_FORMAT({Purchase Date}, ‘YYYY’) - 2015

The DATETIME_FORMAT function returns a string, and it looks like you are trying to subtract a year from it, which doesn’t work because you can’t subtract a number from a string. Try this formula, which gets the year as a number:

YEAR({Purchase Date}) - 2015

Here are some support articles the Switch function and the Formula field reference.

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?


Thanks for trying to help! I really appreciate it. I have changed the formula to what you suggested and am getting the same result.

here is a link to a screenshot of the relevant fields.

The formula I am using is now: SWITCH(YEAR({Purchase Date}) - 2015,1,{Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)},BLANK())

As you can see, the 2016 Depreciation field is not formatting as a number, and when I try to, it says that the result type is not a number.

If I make the formula just: {Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)} it works, and is able to be formatted as a number. But obviously, I need to be able to set up the Switch conditions.?

I’m starting to wonder if Switch returns strings instead of numbers by default, and there’s nothing we can do about it. I even attempted to wrap the entire thing in a VALUE(), and that didn’t help.

Once again, thanks for trying to help!!

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • June 10, 2020

Thanks for trying to help! I really appreciate it. I have changed the formula to what you suggested and am getting the same result.

here is a link to a screenshot of the relevant fields.

The formula I am using is now: SWITCH(YEAR({Purchase Date}) - 2015,1,{Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)},BLANK())

As you can see, the 2016 Depreciation field is not formatting as a number, and when I try to, it says that the result type is not a number.

If I make the formula just: {Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)} it works, and is able to be formatted as a number. But obviously, I need to be able to set up the Switch conditions.?

I’m starting to wonder if Switch returns strings instead of numbers by default, and there’s nothing we can do about it. I even attempted to wrap the entire thing in a VALUE(), and that didn’t help.

Once again, thanks for trying to help!!


Thank you for the screen capture.

A formula field can be formatted as a number only if all possible return values are numbers.

For your SWITCH statement, a possible return value is BLANK(). You can simply remove this last part of the formula, as it isn’t necessary. Then you should be able to format the field as a number.

SWITCH( YEAR({Purchase Date}) - 2015,
  1, {Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)}
)

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?

Justin_Barrett
A
Forum|alt.badge.img+3
  • Alan_Reid
  • Author
  • Participating Frequently
  • June 10, 2020

Thank you for the screen capture.

A formula field can be formatted as a number only if all possible return values are numbers.

For your SWITCH statement, a possible return value is BLANK(). You can simply remove this last part of the formula, as it isn’t necessary. Then you should be able to format the field as a number.

SWITCH( YEAR({Purchase Date}) - 2015,
  1, {Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)}
)

If this answers your question, please mark this post as the solution. Otherwise, could you please give a bit more details and a screen capture?


Interesting, that at least let me choose a number format… It’s interesting because BLANK() in an if statement counts as a number. Perhaps this is a bug…

Terms & ConditionsAccessibility statement