dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
317
share rss forum feed


Hall
Premium,MVM
join:2000-04-28
Germantown, OH
kudos:2

[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 ?



Blackbird
Built for Speed
Premium
join:2005-01-14
Fort Wayne, IN
kudos:3
Reviews:
·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...
if(a15>0,"",today()-b15-30)
(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...
=if(and((a15>0),((today()-b15-30)<0)),"",today()-b15-30)
(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