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.

Hi,

Although we can probably figure out what you are asking if you show us a
sample of the data and the expected results it is more likely to get you an
answer:
A B C
D
Current Payment Amount Jan Due Date Grace Days Dec Due Date
53.21 1/1/09 11
12/1/08

and so on.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Anna Wood" wrote:

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.

Your request isn't clear. Don't you just want to do a standard receivables
aging report?

In your example, how do you know when the December amount is paid?

To do an aging report, you will need to keep track of when each amount was
billed (ie, the invoice date), and when each amount was paid. I don't see
that your setup allows for this. In addition, I would forget about the grace
days. Simply say the amount is due on 2/5/09. That should make life easier,
and provide just as much information.

Regards,
Fred.

"Anna Wood" wrote in message
...
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.

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" wrote:

Your request isn't clear. Don't you just want to do a standard receivables
aging report?

In your example, how do you know when the December amount is paid?

To do an aging report, you will need to keep track of when each amount was
billed (ie, the invoice date), and when each amount was paid. I don't see
that your setup allows for this. In addition, I would forget about the grace
days. Simply say the amount is due on 2/5/09. That should make life easier,
and provide just as much information.

Regards,
Fred.

"Anna Wood" wrote in message
...
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.

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" wrote in message
...
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" wrote:

Your request isn't clear. Don't you just want to do a standard
receivables
aging report?

In your example, how do you know when the December amount is paid?

To do an aging report, you will need to keep track of when each amount
was
billed (ie, the invoice date), and when each amount was paid. I don't see
that your setup allows for this. In addition, I would forget about the
grace
days. Simply say the amount is due on 2/5/09. That should make life
easier,
and provide just as much information.

Regards,
Fred.

"Anna Wood" wrote in message
...
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.

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" wrote:

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" wrote in message
...
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" wrote:

Your request isn't clear. Don't you just want to do a standard
receivables
aging report?

In your example, how do you know when the December amount is paid?

To do an aging report, you will need to keep track of when each amount
was
billed (ie, the invoice date), and when each amount was paid. I don't see
that your setup allows for this. In addition, I would forget about the
grace
days. Simply say the amount is due on 2/5/09. That should make life
easier,
and provide just as much information.

Regards,
Fred.

"Anna Wood" wrote in message
...
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.

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

Your request isn't clear. Don't you just want to do a standard
receivables
aging report?

In your example, how do you know when the December amount is paid?

To do an aging report, you will need to keep track of when each amount
was
billed (ie, the invoice date), and when each amount was paid. I don't
see
that your setup allows for this. In addition, I would forget about the
grace
days. Simply say the amount is due on 2/5/09. That should make life
easier,
and provide just as much information.

Regards,
Fred.

"Anna Wood" wrote in message
...
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.

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

Your request isn't clear. Don't you just want to do a standard
receivables
aging report?

In your example, how do you know when the December amount is paid?

To do an aging report, you will need to keep track of when each amount
was
billed (ie, the invoice date), and when each amount was paid. I don't
see
that your setup allows for this. In addition, I would forget about the
grace
days. Simply say the amount is due on 2/5/09. That should make life
easier,
and provide just as much information.

Regards,
Fred.

"Anna Wood" wrote in message
...
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.

Without seeing your formulas, I can only guess, but try something like:
I3: =if($a$2-c390,t3,"")
H3: =if(and($a$2-c360,$a$2-c3<=90),t3,"")
G3: =if(and($a$2-c330,$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.

"Anna Wood" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

Your request isn't clear. Don't you just want to do a standard
receivables
aging report?

In your example, how do you know when the December amount is paid?

To do an aging report, you will need to keep track of when each
amount
was
billed (ie, the invoice date), and when each amount was paid. I
don't
see
that your setup allows for this. In addition, I would forget about
the
grace
days. Simply say the amount is due on 2/5/09. That should make life
easier,
and provide just as much information.

Regards,
Fred.

"Anna Wood" wrote in message
...
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.

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


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

"Anna Wood" wrote:

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.