Use IF text lookup in a numeric formula

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
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?
Photo of James Trory

James Trory

  • 946 Points 500 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
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?

Photo of James Trory

James Trory

  • 946 Points 500 badge 2x thumb
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.
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)
Photo of James Trory

James Trory

  • 946 Points 500 badge 2x thumb
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.
Please post your code and the error message
Photo of James Trory

James Trory

  • 946 Points 500 badge 2x thumb
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.
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)
Photo of Dan

Dan

  • 1,558 Points 1k badge 2x thumb
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.
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.