dslreports logo
    All Forums Hot Topics Gallery


how-to block ads

Search Topic:
share rss forum feed

Germantown, OH

[Excel] Payment-how many days late ?

Okay, the subject isn't entirely accurate as I already have that part sorted out. I'm using

=today()-(Cell Reference Here)-30

This works fine but once a payment is rec'd, I want it to blank out. I have a column "Days late" where the above formula goes. Problem is, it continues to calculate this irregardless of if payment has been rec'd. So, I want to add something along the lines of an "IF" statement, like "If (2nd Cell Reference Here) has a value, blank the cell".

How do you do that ?

Built for Speed
Fort Wayne, IN
·Frontier Communi..

One way would be:
=if((Payment-made Cell Reference Here)>0,"",today()-(Cell Reference Here)-30)
or using a more typical example format for an entry on line 15...
(assuming: payment-made amounts (or numerical flag > 0) in column a, date of payment in column b)

Depending on how you're constructing your data table (for a single payer with multiple future payment rows beneath to simply be checked off as payments are sequentially made or for multiple payers with a single data row for each payer that must be re-entered periodically), you may need to consider some other factors. In the first case (single payer), the above equation should suffice. In the second multi-payer case, you have to consider what to do once the 30-day billing cycle ends and a new one starts. At that point, the old payment data is still in the "payment-made Cell Reference" location and will still blank out the equation results for the new cycle unless you delete that old payment value/flag. Assuming the "cell reference here" is a datevalue for when the latest payment was made, you may need to condition the blanking operation (the use of the payment-made value) with an additional test of that last payment date...
=if(and(((Payment-made Cell Reference Here)>0),((today()-(Cell Reference Here)-30)<0)),"",today()-(Cell Reference Here)-30)
or using a more typical example format for the entry on line 15...
(assuming the same cell contents as the earlier equation example)

Another thing to perhaps consider is whether or how to deal with partial-payments. In your concept, any amount (or flag) entered in the payment cell will blank the "days late" result.

The American Republic will endure until the day Congress discovers that it can bribe the public with the public's money. -- A. de Tocqueville