IF AND Formula Issue
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()...
Forum Discussion
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.
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. - yeah