Skip to main content
Question

IF AND Formula Issue

  • January 9, 2019
  • 0 replies
  • 669 views
Forum|alt.badge.img+8
I have to calculate the amount of per diem an associate is to receive. For the most part I can get this to work in Excel with the following formula:
=IF((J2-I2)<=7,(J2-I2)*25,IF(AND((J2-I2)>7,TODAY()<I2),7*25,IF(AND((J2-I2)<7,TODAY()<I2),7)))

I read where Mark Shiner stated for "And" to do something like this: IF(
[Project Actual] = 0 and [Project Forecast] = 0 and [Project Budget] = 0 ,0,

So I tried this
If(
ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])<=7,ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])*25,
If(
ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])> 7 AND Today()<[Hotel Check-In Date],7*25,
If(
ToDays([Hotel Check-Out Date]-[Hotel Check-In Date]),7 AND Today()<[Hotel Check-In Date]),7)))

But I get an error message "the operator 'and' can't be applied to boolean...

Any help would be greatly appreciated





    This topic has been closed for replies.

    I think your last "If" has a "comma" right before your "7" instead of your full boolean expression(In this case, the "If" is trying to check if "ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])" is true or false. Also, I think changing the "AND" to "and" will make the yellow highlight go away.


    Jordan is right, that comma wants to be a <

    that is
    If(
    ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])<=7,ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])*25,
    If(
    ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])> 7 and Today()<[Hotel Check-In Date],7*25,
    If(
    ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])< 7 and Today()<[Hotel Check-In Date]),7)))

    That solves for syntax but I think you still have a problem in that that condition actually won't resolve since it's a subset of your first condition. This will have been the case in your Excel version, too. If I'm understanding your use case correctly, it looks like you want to reorder your conditions.
    @Hanns

    unlike excel you typically do not nest your IFs.

    If(
    ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])<=7,ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])*25,

    ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])> 7 and Today()<[Hotel Check-In Date],7*25,

    ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])< 7 and Today()<[Hotel Check-In Date]),7)


    The IF will keep being evaluated in sequence until it finds a true condition.


    Hans,

    The Quick Base "IF" function is most similar to the Excel "IFS" function.  

    You can list as many different conditions and results that you want with a final default result for when none of the stated conditions are met.  There is no need to embed multiple "IF" functions inside of each other.

    Below is how I like to write them out.

    Example1
    IF  (  Condition1,Result1  ,Condition2,Result2  ,Condition3,Result3  ,default Condition  )

    If one of the conditions is really a combination of multiple conditions then you combine them with "AND", "OR", and parentheses.

    Example2
    IF  (  Condition1,Result1  ,Condition2a AND Condition2b,Result2  ,(Condition3aa AND Condition3ab) OR Condition3b,Result3  ,Default Result  )
    I hope this helps.
    Terms & ConditionsAccessibility statement