I have been researching this for several months with absolutely no success.
I created a spreadsheet to receive rental payments with a $50 late fee after 5 days with a balance owed column. I got it all worked out except for one very frustrating problem:
When I enter their actual payment without late fee after the 5th, excel sees it as paid and removes the late fee. If I enter the payment after the 5th with late fee included, it again sees it as paid and credits them $50 in the balance owed column.
How can I keep the late in intact even after a payment is posted after the 5th?
Example:
A B C D E F G H I J K
Column G: I used =SUM(TODAY()-$M$1) M1 not shown here, it contains due date, to determine how many days late
Column H: I used =IF(AND(G2>5,I2<D2+E2),50,0) To me this says if they are more than 5 days late (G2) and have not paid (I2) then add $50, if they have paid amount owed, then return 0
Column K: I used =IF(AND(G2>5,I2<(D2+E2+50)),I2-(D2+E2+50),I2-(D2+E2)) I had to use part of the Column H formula to prevent a circular reference. I've also tried various incarnations of this formula with no success
AS you can see from my examples, they are all 7 days late generating a $50 late fee. Unit #12 I entered the amount owed without late fee and it removed the late fee and gave them a $0 balance owed
Unit #14 I entered the amount owed with the late fee, it removed the late fee and credited them with $50 on Balance owed
Unit #15 correctly shows total amount owed when late, adding regular monthly payments plus late fee.
Please help - how can I tell excel that if they are late, leave the late fee in even after they pay past the due date?
I created a spreadsheet to receive rental payments with a $50 late fee after 5 days with a balance owed column. I got it all worked out except for one very frustrating problem:
When I enter their actual payment without late fee after the 5th, excel sees it as paid and removes the late fee. If I enter the payment after the 5th with late fee included, it again sees it as paid and credits them $50 in the balance owed column.
How can I keep the late in intact even after a payment is posted after the 5th?
Example:
A B C D E F G H I J K
| Unit # | 1st resident | 2nd resident | Current Monthly Rate | Parking Space Fee | Monthly Total Owed | Days Late | Late Fee ($50) | Paid Amount | K=Checks R=Cash M=Money Order C= Cashiers | Balanced Owed This Month |
| 12 | Joe Blow | Joes Wife | 519.00 | - | 569.00 | 7 | - | 519 | - | |
| 14 | John Doe | Mrs. Doe | 470.00 | 15.00 | 535.00 | 7 | - | 535 | +50 |
| 15 | Janet Jackson | 406.00 | - | 456.00 | 7 | 50 | (456.00) |
Column G: I used =SUM(TODAY()-$M$1) M1 not shown here, it contains due date, to determine how many days late
Column H: I used =IF(AND(G2>5,I2<D2+E2),50,0) To me this says if they are more than 5 days late (G2) and have not paid (I2) then add $50, if they have paid amount owed, then return 0
Column K: I used =IF(AND(G2>5,I2<(D2+E2+50)),I2-(D2+E2+50),I2-(D2+E2)) I had to use part of the Column H formula to prevent a circular reference. I've also tried various incarnations of this formula with no success
AS you can see from my examples, they are all 7 days late generating a $50 late fee. Unit #12 I entered the amount owed without late fee and it removed the late fee and gave them a $0 balance owed
Unit #14 I entered the amount owed with the late fee, it removed the late fee and credited them with $50 on Balance owed
Unit #15 correctly shows total amount owed when late, adding regular monthly payments plus late fee.
Please help - how can I tell excel that if they are late, leave the late fee in even after they pay past the due date?
