IF AND Formula Issue

Question

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

jordanmcalister · Answer

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.

alexcertificati · Answer

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.

alexcertificati · Answer

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.

quickbasecoachd · Answer

@Hannsunlike excel you typically do not nest your IFs.If(ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])&lt;=7,ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])*25,ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])&gt; 7 and Today()&lt;[Hotel Check-In Date],7*25,ToDays([Hotel Check-Out Date]-[Hotel Check-In Date])&lt; 7 and Today()&lt;[Hotel Check-In Date]),7)The IF will keep being evaluated in sequence until it finds a true condition.

alexcertificati · Answer

yeah

Forum Discussion

IF AND Formula Issue

Related Content

Missing Records/Data Issue

Quickbase App Issues

MIME Pipeline issue

Formula Assistance

Date Field Formula