Skip to main content
Question

Use IF text lookup in a numeric formula

  • January 30, 2019
  • 0 replies
  • 118 views
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?
    This topic has been closed for replies.

    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?

    • JamesTrory
    • Author
    • Registered
    • January 30, 2019
    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)
    • JamesTrory
    • Author
    • Registered
    • January 30, 2019
    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
    • JamesTrory
    • Author
    • Registered
    • January 30, 2019
    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.
    Terms & ConditionsAccessibility statement