IF AND Formula Issue

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
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





Photo of Hans Hamm

Hans Hamm

  • 710 Points 500 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Jordan McAlister

Jordan McAlister

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


Photo of Forrest Parker

Forrest Parker

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