if formula

[Sue]

Need help with this formula.
If AH12 minus AL7 is greater than 0 but less than 30, then AH12 minus AL7
times AE7, if AH12 minus AL7 is greater than 30, then return value in AE7, if
AH12 minus AL7 is less than 0, return blank.

(AH12 and AL7 are dates)
AE7 is a monthly rate.

 

[David Biddulph]

=IF(AH12-AL7<0,"",IF(AH12-AL7>30,AE7,AH12-AL7*AE7))
or you may have intended
=IF(AH12-AL7<0,"",IF(AH12-AL7>30,AE7,(AH12-AL7)*AE7))

Adjust to suit what you want it to do if AH12-AL7 is equal to either zero or
30, as those conditions were undefined in your question.

Note also that you are multiplying a value in days by what you refer to as a
"monthly rate", so you may want to adjust that too.

 

[Pete_UK]

Try it this way:

=IF(AH12-AL7>30,AE7,IF(AH12-AL7>0,(AH12-AL7)*AE7,""))

For it to make sense, you may need to change AE7 to a daily rate, as that is
what you are multiplying by in the middle (and the first term returned will
then be 30*AE7), or divide the middle term by 30.

Hope this helps.

Pete

 

[Sue]

I adjusted and it works wonderfully. Thanks

=IF(AH12-AL7<0,"",IF(AH12-AL7>30,AE7,AH12-AL7*AE7))
or you may have intended
=IF(AH12-AL7<0,"",IF(AH12-AL7>30,AE7,(AH12-AL7)*AE7))

Adjust to suit what you want it to do if AH12-AL7 is equal to either zero or
30, as those conditions were undefined in your question.

Note also that you are multiplying a value in days by what you refer to as a
"monthly rate", so you may want to adjust that too.