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.

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?

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:

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.

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.

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…