0
I'm trying to have a cell show $0 (no late fee) between 1st-5th of each month, after the 5th have the same cell show $20 (late fee) unless payment is received, using date stamp to determine if payment was received on time.
=IF(AND(TODAY()>A7,TODAY()<=B7),"20","0")
Referencing A7 as 7/5/19 and B7 as 7/31/19 (unless there's a better way).
So I need that formula to be able to run only if payment is not made between the 1st and the 5th.
=IF(AND(D11<>""),(TODAY()>A7,TODAY()<=B7),"20","0")
If a certain cell in which I have a timestamp function (D11) shows the date then this formula won't run, but this formula isn't working either.
ants fax
Posted 2019-07-01T15:28:35.923
Reputation: 1
What formula(s) have you tried so far? Please click [edit] and put them in your question. Please do not click Add Comment, for when new comments arrive they can push old comments off screen. – K7AAY – 2019-07-01T15:54:38.763
A couple of thoughts: 1. TODAY() is volatile. If you base the late fee on it, it won't be a stable value. You receive payment on the 2nd of the month, so no late fee. On the 6th of the month, the value will change to $20. It would be better to add the payment date to a cell, then reference that cell. You can use the keyboard shortcut
Ctrl-Semicolonto insert todays date. 2. If you compare the payment date to calendar dates in reference cells, you'll need to update the dates every month. You'll save work by using functions to derive those. (cont'd) – fixer1234 – 2019-07-02T03:34:52.550For example, say you insert today's payment date in some cell I'll call "date". To see if that is between the 1st and the 5th of the month, test:
DAY(date)<6. DAY returns the day of the month for a date. 3. Testing the last day of the month doesn't really buy you anything. However, you need a basis to know what month the payment is due. Suppose someone misses the month entirely and pays this month's bill on the 2nd of next month? It needs to know that a late fee applies, even though it's before the 6th of that month. (cont'd) – fixer1234 – 2019-07-02T03:35:12.943You can handle both conditions in one test. Using one form or another, generate a due date for the current payment using a formula that you don't need to change every month; include a mechanism for handling an entirely missed month. That would be the 5th day of this month (stored as a specific date). Then compare the payment date to that due date. If the payment date is later, there is a late fee. 4. All formulas always run. You don't have a value that determines whether or not a formula works. (cont'd) – fixer1234 – 2019-07-02T03:35:41.057
You just build that condition into the formula, like you did in your examples. If all conditions are met, the cell has a value of 20. Otherwise, the value is zero. 5. You are using the
0and20results as numbers in calculations. They won't work if you turn them into text by wrapping them in quotes. – fixer1234 – 2019-07-02T03:35:46.800