Discussions

Expand all | Collapse all

IF AND Formula Issue

  • 1.  IF AND Formula Issue

    Posted 01-08-2019 19:48
    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







  • 2.  RE: IF AND Formula Issue

    Posted 01-08-2019 21:02
    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.




  • 3.  RE: IF AND Formula Issue

    Bronze
    Contributor
    Posted 01-08-2019 22:47
    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.


  • 4.  RE: IF AND Formula Issue

    Bronze
    Contributor
    Posted 01-08-2019 22:57
    You might actually be better off with a Min, anyway; it looks like you want to do $25/day for up to 7 days?

    I'd write that
    Min(ToDays([Hotel Check-Out Date]-[Hotel Check-In Date]),7)*25

    though admittedly I don't understand what your last clause is doing (and my Min formula omits it); the concept is length of stay times $25, up to $175, unless the length of stay is less than 7 days and in the future, in which case it's $7?

    I also notice now that your formula as written now is returning a null value if length of stay is greater than 7 and the checkin date is in the past. You've got me curious.


  • 5.  RE: IF AND Formula Issue

    Bronze
    Contributor
    Posted 01-08-2019 23:00
    yeah



  • 6.  RE: IF AND Formula Issue

    Posted 01-08-2019 22:59
    @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.




  • 7.  RE: IF AND Formula Issue

    Posted 01-08-2019 23:11
    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.