Sorting amounts in columns by due dates

[Anna Wood]

I need to add values in different columns of data based on due dates.

Categories of data include:
Current Due Date (say, 2/1/09)
Grace Days (i.e., 5 days)
Current Payment Amount
Jan Due Date (1/1/09)
Jan Payment Amount
Dec Due Date (12/1/08)
Dec Amount

The current payment amount falls into a "Within Grace Days" column if it is
within 5 days of the current due date. I need to lump amounts due for "1-30
Days", "31 to 60 Days", etc. based on the amounts and when the payments were
due.

As today's date changes, the data will need to flow into the respective
delinquency categories as it passes from 30 to 31 days, etc.

I would like to copy this formula down the page and need to use an ISNA or
ISBLANK formula (say they don't owe a Jan or Dec payment) as I don't want a
$0.00 amount in a cell - it needs to stay blank.

Your help is greatly appreciated.

 

[Anna Wood]

We don't do the billing. We are the lender, but we track the payments
received through the servicer's website (bank). It's all manual. We need to
check each day to see what payments posted overnight and remove them from our
report. We only track those who have not made their payment. It's very
similar to a mortgage payment - you have so many days grace before your
payment is technically due.

I searched the discussion groups for a receivable report and didn't see any
other posts. Something along those lines will work. I just need to sort
into the appropriate column based on the day the payment was due and today's
date. Several of our loans have different payment dates (2/1, 2/5, 2/9,
2/16) and different grace days (1,5,10,16). It would be helpful to have an
easy way to automatically lump each loan into the approriate category.

We need to track the loans that are currently in grace so we can make sure
there isn't a problem holding it up from being paid on time prior to it
becoming delinquent.

 

[Fred Smith]

Let's start at the beginning.

You say you receive a list of payments made yesterday. You need to remove
them from your report. How do you know when that payment was due? How do you
manually decide whether it was December's payment, or January's payment, for
example?

Regards,
Fred.

 

[Anna Wood]

The spreadsheet we can access from the bank lists the due date and grace
days. If a payment has been received for Feb, the due date will show 3/1 so
we know a payment was received.

We track this daily and because of the current economic times, are just now
beginning to see people lag more than a month behind, so we are trying to
come up with a better way to track how far behind they are getting.

 

[Fred Smith]

You're not making it easy to provide help. Instead of drip-feeding
information, why don't you give us an example of what you want. What's in
the sheet from the bank, and what output do you want.

Regards,
Fred.

 

[Anna Wood]

A2 = Current Date

I have the Within Grace and 1-30 Days figured out - it's the 31-60, 61-90,
and 90+ formula I can't quite put together.

Sheet1 (current loans)

B3 C3 D3 E3 F3 G3 H3 I3 T3
Loan Pmt Grace Within 1-30 31-60 61-90 91+ Current
Name Date Days Grace Days Days Days Days Pmt $


Sheet2 (past due loan payments)

A1 B1 C1
Loan Pmt Pmt
Name Date $

 

[Fred Smith]

Without seeing your formulas, I can only guess, but try something like:
I3: =if($a$2-c3>90,t3,"")
H3: =if(and($a$2-c3>60,$a$2-c3<=90),t3,"")
G3: =if(and($a$2-c3>30,$a$2-c3<=60),t3,"")

Other suggestions:
-- I don't see the value of having a cell for today's date. Why not just use
today() in all your calculations.
-- I would see value in having a column for Days late (=max(today()-c3,0)).
Then you can use this cell instead of a2-c3 in the above formulas.

Regards,
Fred.

 

[JoeM]

Wouldn't ya know after I built all of this the stupid columns are coming out
as:

120, 120, 30, 60, 90

I need: 30, 60, 90, 120,>120

I know i've seen a post around here about this but I just couldn't find it
again.

Joe
(e-mail address removed)

PS anybody seen a good course online for advanced pivot table training. I
already did the Microsoft one.
Joe