# Use IF text lookup in a numeric formula

• 0
• Question
• Updated 6 months ago
Hi,

I have a numeric formula that calculates the amount of hours that have passed if a date/time exceeds 24 hours.

If([Delivery (hours)]>24,[Delivery (hours)]-24)

I would like to add the condition that if the field [Priority] matches a certain text that it change the result. Something like this:

If(
[Priority]="Normal" AND [Delivery (hours)]>24,[Delivery (hours)]-24),
[Priority]="Rush" AND [Delivery (hours)]>12,[Delivery (hours)]-12)

However because I'm now calling on a Text field, QB complains about it because this is a Numeric formula.

If I convert the formula to Text, now I can't calculate the numbers.

Can anyone help me with a way to do this?
• 946 Points

Posted 6 months ago

• 0
• 72,286 Points
I'm confused, What is wrong with the formula you have as a formula numeric field.  Why do you feel it needs to not be a numeric field type?

• 946 Points
After the first AND in the second row QB says "expecting ," and that there are "extra characters beyond the end of the formula". I assume that means that it doesn't like the AND function and concluded it must be because [Priority] is Text in a Numeric formula.
• 72,286 Points
no its miffed because the formula is not passing the syntax requirements due to mis-matched brackets

I removed the trailing bracket on the line 2 below, as the IF does not end until the end of the formula.

If(
[Priority]="Normal" AND [Delivery (hours)]>24,[Delivery (hours)]-24,
[Priority]="Rush" AND [Delivery (hours)]>12,[Delivery (hours)]-12)
• 946 Points
I noticed the trailing bracket too and removed it, I still get the same error. I copy/pasted your formula above just to be sure... I really don't know then why this formula won't work.
• 72,286 Points
• 946 Points
I figured it out. It's that "AND" is capitalized. I made them lowercase and now it lets me save the formula without errors.

I had no idea that this could cause conflict. Good to know.
• 70,204 Points
Both upper and lower case and AND will both work identically in a formula so that cannot have been the problem, but I'm glad it's now working for you.

If you do not believe me, then change the AND to and, it will still work
(Edited)
• 1,558 Points
I've definitely noticed that the syntax highlighting stuff doesn't always indicate an actual error, and that you can still save and have a working formula. It can be pretty misleading.
• 70,204 Points
Unfortunately it highlights functions with incorrect capitalization, such as isnull would be highlighted as it will be corrected to IsNull on save.  or dbid() will be highlighted.   So those syntax "errors" which will be corrected when the formula saves should not be flagged as warnings.