Help

Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

Formatting as Number in Formula When Number is Manually Entered

Topic Labels: Formulas
Solved
Jump to Solution
859 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Christopher_Can
6 - Interface Innovator
6 - Interface Innovator

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

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

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!