Im going to guess this is pretty easy stuff but cant figure out exactly how, or if, I can do this.Shared album - William McDonald - Google Photos


I have had no training in Excel just pretty much winging it and figuring it out as I go.



On my spreadsheet I was wondering if there is a way to have the LATE FEE column amount added onto the TOTAL DUE column only if the DATE PAID is 6th of the month or after? Does that make sense? Basically if someone pays rent the 6th or later it will automatically add the late fee to the to the total.


I appreciate any input and responses. Thanks so much.

Hi,

There are a couple of ways of doing this, but I would suggest calculating the LATE FEE column itself based on the date - so that you don't have late fees showing for accounts that aren't late.

I did a little interpretation of your pic to have some numbers to play with, and inserted a few dates that were past the due date.

In the Late Fee column, I used a simple "IF" formula to determine whether the date in the date paid column is less than the due date (i.e. the 5th or earlier), and assigning the value "0" if it's on time. If the date is not less than the due date (9/6) it calculates the 5% late fee (by the way, that's 5% by the values you displayed, not .05%).

I added a little bit to the right to add up the amount paid in case anyone would, say, split pay partially in cash and partially by check, and then calculated a balance. I'm not sure whether any of this is desirable.

You could certainly use the same idea with the IF formula directly into the total amount due. In other words, if the date is the 6th or later, add the Late Fee column value to the other charges, and if not, don't.

The thing I'm not sure of is whether you're using this to record transactions that have already happened, or whether you're using it to quickly calculate how much is due. To get the right total, you'd have to start by entering in the current date manually. If you didn't enter the date, you wouldn't get the late value added in, and if you're in real time telling the customer what they owe, the date must be typed in first. I hope that makes sense.

I was toying with using the formula that shows "today's date" that I added at the top, but that won't work because if I paid on the 5th, with no late fee, the spreadsheet would update on the 7th to show I owed a late fee I hadn't owed because I paid on time. You could use a more complex formula that takes into account either the typed in date in the date paid column, or in its absence, today's date, but that got convoluted and that's when I mulled whether this would be used in real-time or to post the day's payments afterwards.

Sorry if I got off course there.

Best,

Ann

Attached Images

If_date_less_than.jpg (181.3 KB, 12 views)

Attached Files

09072023.xlsx (12.3 KB, 1 views)