I'm creating an excel worksheet for some rental properties. Sometimes, the renters will pay less than what their monthly rate is, and will receive a $15 late fee if the total is not received by the 15th of the month. A lot of the renters will make partial payments throughout the month, but again, the total must be on time. This is what I have for instance, for January 2009:

B4 - total monthly rent

D4 - date received

E4- amount

G4 - date received

H4 - amount

J4 - date received

K4 - amount

M4 - late fee ($15)

What I want the formula to do is say: If D4 and G4 and J4 are greater than 1/15/09 AND E4 + H4 + K4 are less than B4, then add $15 in cell M4. In other words if total amount received does not equal the total amount due by the due date, they are charged a late fee.

First, when you say add $15 in cell M4I assume you mean M4 shouldshow$15. Otherwise I don't know what you are adding it to.Next, re:

If D4 and G4 and J4 are greater than 1/15/09.I don't think that's what you really want. What if D4 and G4 are less than 1/15/09 but J4 is greater? Based on your wording, you don't want to see $15 in M4 unless all 3 dates are greater than 1/15/09.

Would simply checking to see if "today" is greater than the 15th and the SUM is less than the Total Due work for you?

=IF(AND(TODAY()>DATEVALUE("1/15/09"),SUM(E4,H4,K4)<B4),"$15","")

Thank you for your post and helping me with my worksheet. I have tried your formula. It works if the total payment(s) received are less than the total due, however if I enter that a renter paid the total rent due but past the due date of 1/15/09, it doesn't show $15 in M4.

I kind of thought you were going to say that... Try this:

=IF(OR(D4>DATEVALUE("1/15/09"),G4>DATEVALUE("1/15/09"),J4>DATEVALUE("1/15/09")),"$15",IF(AND(TODAY()>DATEVALUE("1/15/09"),SUM(E4,H4,K4)<B4),"$15",""))

What happens if they make more than 3 payments?

That works! I hope you aren't sick of me yet, because I have another question. (Sorry, I'm taking an excel class but I haven't gotten that far yet. ) Ok, in cell P4, I want to show the balance left over after they make payments. That is, if they don't pay the full rent, like a lot of them do, they will carry a balance over. So, in P4, I have the formula saying total rent minus all of the payments plus any late charge = the balance. That formula worked before I plugged in the formula you gave me. Now I get a #value! error. However, if M4 shows 15, then the formula works. If it doesn't have a value, then the error. As for you question about making more than 3 payments, they don't usually, but I guess if that ever happens, I'll have to add more columns or add it to a previous payment.

The problem is that the IF statement puts a "" (empty cell) in M4 if here is no late fee due. When you try to perform a mathematical operation with that cell, you get the #VALUE error. Try this one. I've replaced the "" with a 0. I also replaced the "$15" with just 15 so that everything will be numbers.

=IF(OR(D4>DATEVALUE("1/15/09"),G4>DATEVALUE("1/15/09"),J4>DATEVALUE("1/15/09")),15,IF(AND(TODAY()>DATEVALUE("1/15/09"),SUM(E4,H4,K4)<B4),15,0))

BTW... you should select the formula and use Tools...Formula Auditing...Evaluate Formula to single step through the formula with various dates and amounts in the cells. This will help you understand how the formula works. Then you should be able to impress your teacher. :)

Thanks so much! That worked and you've been VERY helpful. Don't know about impressing anyone, especially since you did the hard work, but it always feels good when you can get something to work the way you want it to. Thanks again!!!! Lou

Ask Your Question

Weekly Poll

Do you think Amazon can re-invent the convenience store?

Discuss in The Lounge

Poll History