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.

Formatting as Number in Formula When Number is Manually Entered

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

I have the following formula that spits out a number of days since the last event for an entry and if no event is found, it returns 9999 instead:

IF({Days Since}=0,‘9999’,DATETIME_DIFF(TODAY(),{Days Since},‘days’))

My issue is that the formula field doesn’t recognize this result as a number and won’t let me format and filter as such.

Is there any way I can return a result that allows formatting as a number? I tried using BLANK() in place of 9999, but the issue is that I need to filter results that return a number greater than say 90 or 180. So blank results get filtered out when I would want them to be filtered in. And this is part of a longer filter so I can’t use an OR statement in the filter.

1 Solution

Accepted Solutions

Anytime you put a number inside of quotes you’re telling Airtable to treat it as a string, not a number. Simply remove the quotes around 9999.

IF({Days Since}=0, 9999, DATETIME_DIFF(TODAY(),{Days Since},'days'))

See Solution in Thread

2 Replies 2

Anytime you put a number inside of quotes you’re telling Airtable to treat it as a string, not a number. Simply remove the quotes around 9999.

IF({Days Since}=0, 9999, DATETIME_DIFF(TODAY(),{Days Since},'days'))

Thanks, Kamille. Deceptively simple answer there!