Formatting as Number in Formula When Number is Manually Entered

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.

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'))
2 Likes

Thanks, Kamille. Deceptively simple answer there!

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.