Skip to main content
Solved

Formatting as Number in Formula When Number is Manually Entered

  • August 10, 2021
  • 2 replies
  • 39 views
C
Forum|alt.badge.img+7

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.

Best answer by Kamille_Parks11

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 replies

Kamille_Parks11
Forum|alt.badge.img+27

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'))
E
C
C
Forum|alt.badge.img+7
  • Christopher_Can
  • Author
  • Known Participant
  • August 11, 2021

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!

Terms & ConditionsAccessibility statement