Date/If function for past dues

hi,
in column E you could put

=if(A2<Now(), "Overdue","")

or you could use conditional formating.
go Format>conditional format
enter Cell value is less than =now()

then click the format button>patterns. pick a bright color to high light
the overdue cell.

regards
FSt1

"workin4alivin" wrote:

> I want to be able to put in a formula that will tell me if a loan recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

Hi!

One way is to use conditional formatting to highlight the Due Date cell
based on today's date and whether or not a date has been entered in the
Payment Rec'd cell.

A1 = payment due date
D1 = acutal date payment is rec'd

Select cell A1
Goto Format>Conditional Formatting
Formula is: =AND(A1<>"",TODAY()>=A1,D1="")
Click the Format button and maybe select a background color
OK out

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
>I want to be able to put in a formula that will tell me if a loan
>recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula
> that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

Hi!

>I am not even sure that this is possible because there are so many
>variables

The thing about Excel is that just about anything IS possible. The problem
is whether "we" know how to make it work!

Right now, I'm in the "problem" phase on this one!

Seems to me (based on my experience with credit) that a payment is due
monthly. If I pay more than is due I still have to make a payment next month
and the excess payments are just taken off of the backend of the loan
period.

I'm certain that this could be done but it might require a different layout
of the data but that in itself may be more work than it's worth.

>If nothing jumps out at you, it's not a big deal to look at each page.

Nothing is jumping out at me!

I don't see any relation between any of the data on the Summary sheet and
the data on the other sheets.

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Here's the data that is on my summary page,
>
> Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
> $1,500 $1,225 7/1/2005 $140
> $2,000 $1,915 7/3/2005 $85
> $2,500 $600 7/19/2005 $100
> $2,500 $2,000 8/10/2005 $200
> $1,000 $925 7/6/2005 $75
> $2,400 $200 7/18/2005 $100
> $1,000 $530 7/5/2005 $50
> $1,000 $622 8/5/2005 $42
> $3,000 $2,916 7/1/2005 $84
> $820 $495 7/29/2005 $100
>
> I want the column listed Past Due 30 and Past Due 60 to have something
> automatically entered or formated somehow to show when they are past due
> 30
> or 60 days, based on today's date and information posted under each
> borrowers
> page as the payments come in.
>
> Essentially, I want a formula or something that says: If there isn't a
> payment made on Account #1, Sheet 2, more recently than 30 days ago, the
> cell
> under Past Due 30 days will show Red or Past Due or something.
>
> THe data that is on sheet 2 is below with column headings.
>
> Date Amount Cr. Amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 12/1/2004 25.00 25.00 1/14/2005 ($50) 980
> 4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
> 5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
> 6/1/2005 25.00 25.00
> 7/1/2005 25.00 25.00
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> As you can see, this borrower is not past due. The last payment was 7/29,
> but he paid his September payment too. He will not be past due until
> 11/01/05.
> Below is a borrower that is behind:
> Date Amount Date Amount
> 5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
> 6/13/2005 42.00 ($8.00)
> 7/13/2005 42.00
> 8/13/2005 42.00
> 9/13/2005 42.00
>
> This borrower is behind. the last payment was $50 but it only covered
> 5/13
> and part of 6/13. On the Summary page, I want it to show that this
> borrower
> is past due 30 days. On Monday, she will be past due 60, so when I open
> it
> MOnday, I want it to read 60 days.
>
> I am not even sure that this is possible because there are so many
> variables, thanks for taking the time to look at it though. Please don't
> spend too much time working on it, though. If nothing jumps out at you,
> it's
> not a big deal to look at each page.
>
> Tara
>
> "Biff" wrote:
>
>> Hi!
>>
>> Sorry, I can't figure out what you're trying to do here. I copied the
>> sample
>> data you posted into a sheet but I still can't make heads or tails out
>> it!
>>
>> Biff
>>
>> "workin4alivin" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > FSt1 and Biff,
>> >
>> > Both of your suggestions worked. But my problem now is getting this
>> > information to show on the summary sheet. I want a column that shows
>> > whether
>> > an account is overdue or not, which could allow me to link a cell on
>> > the
>> > summary page to each client loan page.
>> >
>> > I am no expert in Excel, but I was thinking I needed to do some type of
>> > IF
>> > statement. Only, I can't figure one out that would work.
>> >
>> > Below is the data as I have it set up on each clients worksheet.
>> >
>> > Date Amount Cr.amt Date Amount Check #
>> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
>> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
>> > 8/1/2005 25.00 25.00
>> > 9/1/2005 25.00 25.00
>> > 10/1/2005 25.00
>> >
>> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
>> > background.
>> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
>> > formatting-
>> > Column A is pink if before Now and COlumn C formatted if amount is
>> > under
>> > $25.00.
>> > Row 5 is all white, therefore this client is up-to-date on payments.
>> >
>> > This works, but I can't see a way to show on the summary page that the
>> > account is overdue or not. Any help is appreciated! Thanks, Tara
>> >
>> >
>> > "FSt1" wrote:
>> >
>> >> hi,
>> >> forgot to mention.
>> >> highlight the a column and conditional fomat the whole column.
>> >>
>> >> regards
>> >> FSt1
>> >>
>> >> "FSt1" wrote:
>> >>
>> >> > hi,
>> >> > in column E you could put
>> >> >
>> >> > =if(A2<Now(), "Overdue","")
>> >> >
>> >> > or you could use conditional formating.
>> >> > go Format>conditional format
>> >> > enter Cell value is less than =now()
>> >> >
>> >> > then click the format button>patterns. pick a bright color to high
>> >> > light
>> >> > the overdue cell.
>> >> >
>> >> > regards
>> >> > FSt1
>> >> >
>> >> > "workin4alivin" wrote:
>> >> >
>> >> > > I want to be able to put in a formula that will tell me if a loan
>> >> > > recipient's
>> >> > > loan is past due.
>> >> > >
>> >> > > Column A is payment due date
>> >> > > Column B is payment amount due
>> >> > > Column D is acutal date payment is rec'd
>> >> > > Column E is actual amount received
>> >> > >
>> >> > > Any help that anyone can offer will be appreciated, including a
>> >> > > formula that
>> >> > > will work or a different way to set up my workbook to give me what
>> >> > > I
>> >> > > want.
>> >> > > (Essentially, I have a page for each borrower and a summary sheet
>> >> > > on
>> >> > > the
>> >> > > front page that shows original loan balance, balance due, late
>> >> > > payments,
>> >> > > etc.)
>> >> > >
>> >> > > Thank you, Tara
>>
>>
>>

hi,
forgot to mention.
highlight the a column and conditional fomat the whole column.

regards
FSt1

"FSt1" wrote:

> hi,
> in column E you could put
>
> =if(A2<Now(), "Overdue","")
>
> or you could use conditional formating.
> go Format>conditional format
> enter Cell value is less than =now()
>
> then click the format button>patterns. pick a bright color to high light
> the overdue cell.
>
> regards
> FSt1
>
> "workin4alivin" wrote:
>
> > I want to be able to put in a formula that will tell me if a loan recipient's
> > loan is past due.
> >
> > Column A is payment due date
> > Column B is payment amount due
> > Column D is acutal date payment is rec'd
> > Column E is actual amount received
> >
> > Any help that anyone can offer will be appreciated, including a formula that
> > will work or a different way to set up my workbook to give me what I want.
> > (Essentially, I have a page for each borrower and a summary sheet on the
> > front page that shows original loan balance, balance due, late payments,
> > etc.)
> >
> > Thank you, Tara

Biff,

Here's the data that is on my summary page,

Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
$1,500 $1,225 7/1/2005 $140
$2,000 $1,915 7/3/2005 $85
$2,500 $600 7/19/2005 $100
$2,500 $2,000 8/10/2005 $200
$1,000 $925 7/6/2005 $75
$2,400 $200 7/18/2005 $100
$1,000 $530 7/5/2005 $50
$1,000 $622 8/5/2005 $42
$3,000 $2,916 7/1/2005 $84
$820 $495 7/29/2005 $100

I want the column listed Past Due 30 and Past Due 60 to have something
automatically entered or formated somehow to show when they are past due 30
or 60 days, based on today's date and information posted under each borrowers
page as the payments come in.

Essentially, I want a formula or something that says: If there isn't a
payment made on Account #1, Sheet 2, more recently than 30 days ago, the cell
under Past Due 30 days will show Red or Past Due or something.

THe data that is on sheet 2 is below with column headings.

Date Amount Cr. Amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
12/1/2004 25.00 25.00 1/14/2005 ($50) 980
4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
6/1/2005 25.00 25.00
7/1/2005 25.00 25.00
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

As you can see, this borrower is not past due. The last payment was 7/29,
but he paid his September payment too. He will not be past due until
11/01/05.
Below is a borrower that is behind:
Date Amount Date Amount
5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
6/13/2005 42.00 ($8.00)
7/13/2005 42.00
8/13/2005 42.00
9/13/2005 42.00

This borrower is behind. the last payment was $50 but it only covered 5/13
and part of 6/13. On the Summary page, I want it to show that this borrower
is past due 30 days. On Monday, she will be past due 60, so when I open it
MOnday, I want it to read 60 days.

I am not even sure that this is possible because there are so many
variables, thanks for taking the time to look at it though. Please don't
spend too much time working on it, though. If nothing jumps out at you, it's
not a big deal to look at each page.

Tara

"Biff" wrote:

> Hi!
>
> Sorry, I can't figure out what you're trying to do here. I copied the sample
> data you posted into a sheet but I still can't make heads or tails out it!
>
> Biff
>
> "workin4alivin" <[email protected]> wrote in message
> news:[email protected]...
> > FSt1 and Biff,
> >
> > Both of your suggestions worked. But my problem now is getting this
> > information to show on the summary sheet. I want a column that shows
> > whether
> > an account is overdue or not, which could allow me to link a cell on the
> > summary page to each client loan page.
> >
> > I am no expert in Excel, but I was thinking I needed to do some type of IF
> > statement. Only, I can't figure one out that would work.
> >
> > Below is the data as I have it set up on each clients worksheet.
> >
> > Date Amount Cr.amt Date Amount Check #
> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> > 8/1/2005 25.00 25.00
> > 9/1/2005 25.00 25.00
> > 10/1/2005 25.00
> >
> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> > background.
> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> > formatting-
> > Column A is pink if before Now and COlumn C formatted if amount is under
> > $25.00.
> > Row 5 is all white, therefore this client is up-to-date on payments.
> >
> > This works, but I can't see a way to show on the summary page that the
> > account is overdue or not. Any help is appreciated! Thanks, Tara
> >
> >
> > "FSt1" wrote:
> >
> >> hi,
> >> forgot to mention.
> >> highlight the a column and conditional fomat the whole column.
> >>
> >> regards
> >> FSt1
> >>
> >> "FSt1" wrote:
> >>
> >> > hi,
> >> > in column E you could put
> >> >
> >> > =if(A2<Now(), "Overdue","")
> >> >
> >> > or you could use conditional formating.
> >> > go Format>conditional format
> >> > enter Cell value is less than =now()
> >> >
> >> > then click the format button>patterns. pick a bright color to high
> >> > light
> >> > the overdue cell.
> >> >
> >> > regards
> >> > FSt1
> >> >
> >> > "workin4alivin" wrote:
> >> >
> >> > > I want to be able to put in a formula that will tell me if a loan
> >> > > recipient's
> >> > > loan is past due.
> >> > >
> >> > > Column A is payment due date
> >> > > Column B is payment amount due
> >> > > Column D is acutal date payment is rec'd
> >> > > Column E is actual amount received
> >> > >
> >> > > Any help that anyone can offer will be appreciated, including a
> >> > > formula that
> >> > > will work or a different way to set up my workbook to give me what I
> >> > > want.
> >> > > (Essentially, I have a page for each borrower and a summary sheet on
> >> > > the
> >> > > front page that shows original loan balance, balance due, late
> >> > > payments,
> >> > > etc.)
> >> > >
> >> > > Thank you, Tara
>
>
>

FSt1 and Biff,

Both of your suggestions worked. But my problem now is getting this
information to show on the summary sheet. I want a column that shows whether
an account is overdue or not, which could allow me to link a cell on the
summary page to each client loan page.

I am no expert in Excel, but I was thinking I needed to do some type of IF
statement. Only, I can't figure one out that would work.

Below is the data as I have it set up on each clients worksheet.

Date Amount Cr.amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
10/1/2004 25.00 25.00 9/18/2004 ($75) 914
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
background.
Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally formatting-
Column A is pink if before Now and COlumn C formatted if amount is under
$25.00.
Row 5 is all white, therefore this client is up-to-date on payments.

This works, but I can't see a way to show on the summary page that the
account is overdue or not. Any help is appreciated! Thanks, Tara


"FSt1" wrote:

> hi,
> forgot to mention.
> highlight the a column and conditional fomat the whole column.
>
> regards
> FSt1
>
> "FSt1" wrote:
>
> > hi,
> > in column E you could put
> >
> > =if(A2<Now(), "Overdue","")
> >
> > or you could use conditional formating.
> > go Format>conditional format
> > enter Cell value is less than =now()
> >
> > then click the format button>patterns. pick a bright color to high light
> > the overdue cell.
> >
> > regards
> > FSt1
> >
> > "workin4alivin" wrote:
> >
> > > I want to be able to put in a formula that will tell me if a loan recipient's
> > > loan is past due.
> > >
> > > Column A is payment due date
> > > Column B is payment amount due
> > > Column D is acutal date payment is rec'd
> > > Column E is actual amount received
> > >
> > > Any help that anyone can offer will be appreciated, including a formula that
> > > will work or a different way to set up my workbook to give me what I want.
> > > (Essentially, I have a page for each borrower and a summary sheet on the
> > > front page that shows original loan balance, balance due, late payments,
> > > etc.)
> > >
> > > Thank you, Tara

Hi!

Sorry, I can't figure out what you're trying to do here. I copied the sample
data you posted into a sheet but I still can't make heads or tails out it!

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> FSt1 and Biff,
>
> Both of your suggestions worked. But my problem now is getting this
> information to show on the summary sheet. I want a column that shows
> whether
> an account is overdue or not, which could allow me to link a cell on the
> summary page to each client loan page.
>
> I am no expert in Excel, but I was thinking I needed to do some type of IF
> statement. Only, I can't figure one out that would work.
>
> Below is the data as I have it set up on each clients worksheet.
>
> Date Amount Cr.amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> background.
> Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> formatting-
> Column A is pink if before Now and COlumn C formatted if amount is under
> $25.00.
> Row 5 is all white, therefore this client is up-to-date on payments.
>
> This works, but I can't see a way to show on the summary page that the
> account is overdue or not. Any help is appreciated! Thanks, Tara
>
>
> "FSt1" wrote:
>
>> hi,
>> forgot to mention.
>> highlight the a column and conditional fomat the whole column.
>>
>> regards
>> FSt1
>>
>> "FSt1" wrote:
>>
>> > hi,
>> > in column E you could put
>> >
>> > =if(A2<Now(), "Overdue","")
>> >
>> > or you could use conditional formating.
>> > go Format>conditional format
>> > enter Cell value is less than =now()
>> >
>> > then click the format button>patterns. pick a bright color to high
>> > light
>> > the overdue cell.
>> >
>> > regards
>> > FSt1
>> >
>> > "workin4alivin" wrote:
>> >
>> > > I want to be able to put in a formula that will tell me if a loan
>> > > recipient's
>> > > loan is past due.
>> > >
>> > > Column A is payment due date
>> > > Column B is payment amount due
>> > > Column D is acutal date payment is rec'd
>> > > Column E is actual amount received
>> > >
>> > > Any help that anyone can offer will be appreciated, including a
>> > > formula that
>> > > will work or a different way to set up my workbook to give me what I
>> > > want.
>> > > (Essentially, I have a page for each borrower and a summary sheet on
>> > > the
>> > > front page that shows original loan balance, balance due, late
>> > > payments,
>> > > etc.)
>> > >
>> > > Thank you, Tara

FSt1 and Biff,

Both of your suggestions worked. But my problem now is getting this
information to show on the summary sheet. I want a column that shows whether
an account is overdue or not, which could allow me to link a cell on the
summary page to each client loan page.

I am no expert in Excel, but I was thinking I needed to do some type of IF
statement. Only, I can't figure one out that would work.

Below is the data as I have it set up on each clients worksheet.

Date Amount Cr.amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
10/1/2004 25.00 25.00 9/18/2004 ($75) 914
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
background.
Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally formatting-
Column A is pink if before Now and COlumn C formatted if amount is under
$25.00.
Row 5 is all white, therefore this client is up-to-date on payments.

This works, but I can't see a way to show on the summary page that the
account is overdue or not. Any help is appreciated! Thanks, Tara


"FSt1" wrote:

> hi,
> forgot to mention.
> highlight the a column and conditional fomat the whole column.
>
> regards
> FSt1
>
> "FSt1" wrote:
>
> > hi,
> > in column E you could put
> >
> > =if(A2<Now(), "Overdue","")
> >
> > or you could use conditional formating.
> > go Format>conditional format
> > enter Cell value is less than =now()
> >
> > then click the format button>patterns. pick a bright color to high light
> > the overdue cell.
> >
> > regards
> > FSt1
> >
> > "workin4alivin" wrote:
> >
> > > I want to be able to put in a formula that will tell me if a loan recipient's
> > > loan is past due.
> > >
> > > Column A is payment due date
> > > Column B is payment amount due
> > > Column D is acutal date payment is rec'd
> > > Column E is actual amount received
> > >
> > > Any help that anyone can offer will be appreciated, including a formula that
> > > will work or a different way to set up my workbook to give me what I want.
> > > (Essentially, I have a page for each borrower and a summary sheet on the
> > > front page that shows original loan balance, balance due, late payments,
> > > etc.)
> > >
> > > Thank you, Tara

Hi!

>I am not even sure that this is possible because there are so many
>variables

The thing about Excel is that just about anything IS possible. The problem
is whether "we" know how to make it work!

Right now, I'm in the "problem" phase on this one!

Seems to me (based on my experience with credit) that a payment is due
monthly. If I pay more than is due I still have to make a payment next month
and the excess payments are just taken off of the backend of the loan
period.

I'm certain that this could be done but it might require a different layout
of the data but that in itself may be more work than it's worth.

>If nothing jumps out at you, it's not a big deal to look at each page.

Nothing is jumping out at me!

I don't see any relation between any of the data on the Summary sheet and
the data on the other sheets.

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Here's the data that is on my summary page,
>
> Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
> $1,500 $1,225 7/1/2005 $140
> $2,000 $1,915 7/3/2005 $85
> $2,500 $600 7/19/2005 $100
> $2,500 $2,000 8/10/2005 $200
> $1,000 $925 7/6/2005 $75
> $2,400 $200 7/18/2005 $100
> $1,000 $530 7/5/2005 $50
> $1,000 $622 8/5/2005 $42
> $3,000 $2,916 7/1/2005 $84
> $820 $495 7/29/2005 $100
>
> I want the column listed Past Due 30 and Past Due 60 to have something
> automatically entered or formated somehow to show when they are past due
> 30
> or 60 days, based on today's date and information posted under each
> borrowers
> page as the payments come in.
>
> Essentially, I want a formula or something that says: If there isn't a
> payment made on Account #1, Sheet 2, more recently than 30 days ago, the
> cell
> under Past Due 30 days will show Red or Past Due or something.
>
> THe data that is on sheet 2 is below with column headings.
>
> Date Amount Cr. Amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 12/1/2004 25.00 25.00 1/14/2005 ($50) 980
> 4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
> 5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
> 6/1/2005 25.00 25.00
> 7/1/2005 25.00 25.00
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> As you can see, this borrower is not past due. The last payment was 7/29,
> but he paid his September payment too. He will not be past due until
> 11/01/05.
> Below is a borrower that is behind:
> Date Amount Date Amount
> 5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
> 6/13/2005 42.00 ($8.00)
> 7/13/2005 42.00
> 8/13/2005 42.00
> 9/13/2005 42.00
>
> This borrower is behind. the last payment was $50 but it only covered
> 5/13
> and part of 6/13. On the Summary page, I want it to show that this
> borrower
> is past due 30 days. On Monday, she will be past due 60, so when I open
> it
> MOnday, I want it to read 60 days.
>
> I am not even sure that this is possible because there are so many
> variables, thanks for taking the time to look at it though. Please don't
> spend too much time working on it, though. If nothing jumps out at you,
> it's
> not a big deal to look at each page.
>
> Tara
>
> "Biff" wrote:
>
>> Hi!
>>
>> Sorry, I can't figure out what you're trying to do here. I copied the
>> sample
>> data you posted into a sheet but I still can't make heads or tails out
>> it!
>>
>> Biff
>>
>> "workin4alivin" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > FSt1 and Biff,
>> >
>> > Both of your suggestions worked. But my problem now is getting this
>> > information to show on the summary sheet. I want a column that shows
>> > whether
>> > an account is overdue or not, which could allow me to link a cell on
>> > the
>> > summary page to each client loan page.
>> >
>> > I am no expert in Excel, but I was thinking I needed to do some type of
>> > IF
>> > statement. Only, I can't figure one out that would work.
>> >
>> > Below is the data as I have it set up on each clients worksheet.
>> >
>> > Date Amount Cr.amt Date Amount Check #
>> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
>> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
>> > 8/1/2005 25.00 25.00
>> > 9/1/2005 25.00 25.00
>> > 10/1/2005 25.00
>> >
>> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
>> > background.
>> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
>> > formatting-
>> > Column A is pink if before Now and COlumn C formatted if amount is
>> > under
>> > $25.00.
>> > Row 5 is all white, therefore this client is up-to-date on payments.
>> >
>> > This works, but I can't see a way to show on the summary page that the
>> > account is overdue or not. Any help is appreciated! Thanks, Tara
>> >
>> >
>> > "FSt1" wrote:
>> >
>> >> hi,
>> >> forgot to mention.
>> >> highlight the a column and conditional fomat the whole column.
>> >>
>> >> regards
>> >> FSt1
>> >>
>> >> "FSt1" wrote:
>> >>
>> >> > hi,
>> >> > in column E you could put
>> >> >
>> >> > =if(A2<Now(), "Overdue","")
>> >> >
>> >> > or you could use conditional formating.
>> >> > go Format>conditional format
>> >> > enter Cell value is less than =now()
>> >> >
>> >> > then click the format button>patterns. pick a bright color to high
>> >> > light
>> >> > the overdue cell.
>> >> >
>> >> > regards
>> >> > FSt1
>> >> >
>> >> > "workin4alivin" wrote:
>> >> >
>> >> > > I want to be able to put in a formula that will tell me if a loan
>> >> > > recipient's
>> >> > > loan is past due.
>> >> > >
>> >> > > Column A is payment due date
>> >> > > Column B is payment amount due
>> >> > > Column D is acutal date payment is rec'd
>> >> > > Column E is actual amount received
>> >> > >
>> >> > > Any help that anyone can offer will be appreciated, including a
>> >> > > formula that
>> >> > > will work or a different way to set up my workbook to give me what
>> >> > > I
>> >> > > want.
>> >> > > (Essentially, I have a page for each borrower and a summary sheet
>> >> > > on
>> >> > > the
>> >> > > front page that shows original loan balance, balance due, late
>> >> > > payments,
>> >> > > etc.)
>> >> > >
>> >> > > Thank you, Tara
>>
>>
>>

Biff,

Here's the data that is on my summary page,

Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
$1,500 $1,225 7/1/2005 $140
$2,000 $1,915 7/3/2005 $85
$2,500 $600 7/19/2005 $100
$2,500 $2,000 8/10/2005 $200
$1,000 $925 7/6/2005 $75
$2,400 $200 7/18/2005 $100
$1,000 $530 7/5/2005 $50
$1,000 $622 8/5/2005 $42
$3,000 $2,916 7/1/2005 $84
$820 $495 7/29/2005 $100

I want the column listed Past Due 30 and Past Due 60 to have something
automatically entered or formated somehow to show when they are past due 30
or 60 days, based on today's date and information posted under each borrowers
page as the payments come in.

Essentially, I want a formula or something that says: If there isn't a
payment made on Account #1, Sheet 2, more recently than 30 days ago, the cell
under Past Due 30 days will show Red or Past Due or something.

THe data that is on sheet 2 is below with column headings.

Date Amount Cr. Amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
12/1/2004 25.00 25.00 1/14/2005 ($50) 980
4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
6/1/2005 25.00 25.00
7/1/2005 25.00 25.00
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

As you can see, this borrower is not past due. The last payment was 7/29,
but he paid his September payment too. He will not be past due until
11/01/05.
Below is a borrower that is behind:
Date Amount Date Amount
5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
6/13/2005 42.00 ($8.00)
7/13/2005 42.00
8/13/2005 42.00
9/13/2005 42.00

This borrower is behind. the last payment was $50 but it only covered 5/13
and part of 6/13. On the Summary page, I want it to show that this borrower
is past due 30 days. On Monday, she will be past due 60, so when I open it
MOnday, I want it to read 60 days.

I am not even sure that this is possible because there are so many
variables, thanks for taking the time to look at it though. Please don't
spend too much time working on it, though. If nothing jumps out at you, it's
not a big deal to look at each page.

Tara

"Biff" wrote:

> Hi!
>
> Sorry, I can't figure out what you're trying to do here. I copied the sample
> data you posted into a sheet but I still can't make heads or tails out it!
>
> Biff
>
> "workin4alivin" <[email protected]> wrote in message
> news:[email protected]...
> > FSt1 and Biff,
> >
> > Both of your suggestions worked. But my problem now is getting this
> > information to show on the summary sheet. I want a column that shows
> > whether
> > an account is overdue or not, which could allow me to link a cell on the
> > summary page to each client loan page.
> >
> > I am no expert in Excel, but I was thinking I needed to do some type of IF
> > statement. Only, I can't figure one out that would work.
> >
> > Below is the data as I have it set up on each clients worksheet.
> >
> > Date Amount Cr.amt Date Amount Check #
> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> > 8/1/2005 25.00 25.00
> > 9/1/2005 25.00 25.00
> > 10/1/2005 25.00
> >
> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> > background.
> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> > formatting-
> > Column A is pink if before Now and COlumn C formatted if amount is under
> > $25.00.
> > Row 5 is all white, therefore this client is up-to-date on payments.
> >
> > This works, but I can't see a way to show on the summary page that the
> > account is overdue or not. Any help is appreciated! Thanks, Tara
> >
> >
> > "FSt1" wrote:
> >
> >> hi,
> >> forgot to mention.
> >> highlight the a column and conditional fomat the whole column.
> >>
> >> regards
> >> FSt1
> >>
> >> "FSt1" wrote:
> >>
> >> > hi,
> >> > in column E you could put
> >> >
> >> > =if(A2<Now(), "Overdue","")
> >> >
> >> > or you could use conditional formating.
> >> > go Format>conditional format
> >> > enter Cell value is less than =now()
> >> >
> >> > then click the format button>patterns. pick a bright color to high
> >> > light
> >> > the overdue cell.
> >> >
> >> > regards
> >> > FSt1
> >> >
> >> > "workin4alivin" wrote:
> >> >
> >> > > I want to be able to put in a formula that will tell me if a loan
> >> > > recipient's
> >> > > loan is past due.
> >> > >
> >> > > Column A is payment due date
> >> > > Column B is payment amount due
> >> > > Column D is acutal date payment is rec'd
> >> > > Column E is actual amount received
> >> > >
> >> > > Any help that anyone can offer will be appreciated, including a
> >> > > formula that
> >> > > will work or a different way to set up my workbook to give me what I
> >> > > want.
> >> > > (Essentially, I have a page for each borrower and a summary sheet on
> >> > > the
> >> > > front page that shows original loan balance, balance due, late
> >> > > payments,
> >> > > etc.)
> >> > >
> >> > > Thank you, Tara
>
>
>

Hi!

Sorry, I can't figure out what you're trying to do here. I copied the sample
data you posted into a sheet but I still can't make heads or tails out it!

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> FSt1 and Biff,
>
> Both of your suggestions worked. But my problem now is getting this
> information to show on the summary sheet. I want a column that shows
> whether
> an account is overdue or not, which could allow me to link a cell on the
> summary page to each client loan page.
>
> I am no expert in Excel, but I was thinking I needed to do some type of IF
> statement. Only, I can't figure one out that would work.
>
> Below is the data as I have it set up on each clients worksheet.
>
> Date Amount Cr.amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> background.
> Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> formatting-
> Column A is pink if before Now and COlumn C formatted if amount is under
> $25.00.
> Row 5 is all white, therefore this client is up-to-date on payments.
>
> This works, but I can't see a way to show on the summary page that the
> account is overdue or not. Any help is appreciated! Thanks, Tara
>
>
> "FSt1" wrote:
>
>> hi,
>> forgot to mention.
>> highlight the a column and conditional fomat the whole column.
>>
>> regards
>> FSt1
>>
>> "FSt1" wrote:
>>
>> > hi,
>> > in column E you could put
>> >
>> > =if(A2<Now(), "Overdue","")
>> >
>> > or you could use conditional formating.
>> > go Format>conditional format
>> > enter Cell value is less than =now()
>> >
>> > then click the format button>patterns. pick a bright color to high
>> > light
>> > the overdue cell.
>> >
>> > regards
>> > FSt1
>> >
>> > "workin4alivin" wrote:
>> >
>> > > I want to be able to put in a formula that will tell me if a loan
>> > > recipient's
>> > > loan is past due.
>> > >
>> > > Column A is payment due date
>> > > Column B is payment amount due
>> > > Column D is acutal date payment is rec'd
>> > > Column E is actual amount received
>> > >
>> > > Any help that anyone can offer will be appreciated, including a
>> > > formula that
>> > > will work or a different way to set up my workbook to give me what I
>> > > want.
>> > > (Essentially, I have a page for each borrower and a summary sheet on
>> > > the
>> > > front page that shows original loan balance, balance due, late
>> > > payments,
>> > > etc.)
>> > >
>> > > Thank you, Tara

hi,
forgot to mention.
highlight the a column and conditional fomat the whole column.

regards
FSt1

"FSt1" wrote:

> hi,
> in column E you could put
>
> =if(A2<Now(), "Overdue","")
>
> or you could use conditional formating.
> go Format>conditional format
> enter Cell value is less than =now()
>
> then click the format button>patterns. pick a bright color to high light
> the overdue cell.
>
> regards
> FSt1
>
> "workin4alivin" wrote:
>
> > I want to be able to put in a formula that will tell me if a loan recipient's
> > loan is past due.
> >
> > Column A is payment due date
> > Column B is payment amount due
> > Column D is acutal date payment is rec'd
> > Column E is actual amount received
> >
> > Any help that anyone can offer will be appreciated, including a formula that
> > will work or a different way to set up my workbook to give me what I want.
> > (Essentially, I have a page for each borrower and a summary sheet on the
> > front page that shows original loan balance, balance due, late payments,
> > etc.)
> >
> > Thank you, Tara

Hi!

One way is to use conditional formatting to highlight the Due Date cell
based on today's date and whether or not a date has been entered in the
Payment Rec'd cell.

A1 = payment due date
D1 = acutal date payment is rec'd

Select cell A1
Goto Format>Conditional Formatting
Formula is: =AND(A1<>"",TODAY()>=A1,D1="")
Click the Format button and maybe select a background color
OK out

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
>I want to be able to put in a formula that will tell me if a loan
>recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula
> that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

hi,
in column E you could put

=if(A2<Now(), "Overdue","")

or you could use conditional formating.
go Format>conditional format
enter Cell value is less than =now()

then click the format button>patterns. pick a bright color to high light
the overdue cell.

regards
FSt1

"workin4alivin" wrote:

> I want to be able to put in a formula that will tell me if a loan recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

Hi!

>I am not even sure that this is possible because there are so many
>variables

The thing about Excel is that just about anything IS possible. The problem
is whether "we" know how to make it work!

Right now, I'm in the "problem" phase on this one!

Seems to me (based on my experience with credit) that a payment is due
monthly. If I pay more than is due I still have to make a payment next month
and the excess payments are just taken off of the backend of the loan
period.

I'm certain that this could be done but it might require a different layout
of the data but that in itself may be more work than it's worth.

>If nothing jumps out at you, it's not a big deal to look at each page.

Nothing is jumping out at me!

I don't see any relation between any of the data on the Summary sheet and
the data on the other sheets.

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Here's the data that is on my summary page,
>
> Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
> $1,500 $1,225 7/1/2005 $140
> $2,000 $1,915 7/3/2005 $85
> $2,500 $600 7/19/2005 $100
> $2,500 $2,000 8/10/2005 $200
> $1,000 $925 7/6/2005 $75
> $2,400 $200 7/18/2005 $100
> $1,000 $530 7/5/2005 $50
> $1,000 $622 8/5/2005 $42
> $3,000 $2,916 7/1/2005 $84
> $820 $495 7/29/2005 $100
>
> I want the column listed Past Due 30 and Past Due 60 to have something
> automatically entered or formated somehow to show when they are past due
> 30
> or 60 days, based on today's date and information posted under each
> borrowers
> page as the payments come in.
>
> Essentially, I want a formula or something that says: If there isn't a
> payment made on Account #1, Sheet 2, more recently than 30 days ago, the
> cell
> under Past Due 30 days will show Red or Past Due or something.
>
> THe data that is on sheet 2 is below with column headings.
>
> Date Amount Cr. Amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 12/1/2004 25.00 25.00 1/14/2005 ($50) 980
> 4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
> 5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
> 6/1/2005 25.00 25.00
> 7/1/2005 25.00 25.00
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> As you can see, this borrower is not past due. The last payment was 7/29,
> but he paid his September payment too. He will not be past due until
> 11/01/05.
> Below is a borrower that is behind:
> Date Amount Date Amount
> 5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
> 6/13/2005 42.00 ($8.00)
> 7/13/2005 42.00
> 8/13/2005 42.00
> 9/13/2005 42.00
>
> This borrower is behind. the last payment was $50 but it only covered
> 5/13
> and part of 6/13. On the Summary page, I want it to show that this
> borrower
> is past due 30 days. On Monday, she will be past due 60, so when I open
> it
> MOnday, I want it to read 60 days.
>
> I am not even sure that this is possible because there are so many
> variables, thanks for taking the time to look at it though. Please don't
> spend too much time working on it, though. If nothing jumps out at you,
> it's
> not a big deal to look at each page.
>
> Tara
>
> "Biff" wrote:
>
>> Hi!
>>
>> Sorry, I can't figure out what you're trying to do here. I copied the
>> sample
>> data you posted into a sheet but I still can't make heads or tails out
>> it!
>>
>> Biff
>>
>> "workin4alivin" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > FSt1 and Biff,
>> >
>> > Both of your suggestions worked. But my problem now is getting this
>> > information to show on the summary sheet. I want a column that shows
>> > whether
>> > an account is overdue or not, which could allow me to link a cell on
>> > the
>> > summary page to each client loan page.
>> >
>> > I am no expert in Excel, but I was thinking I needed to do some type of
>> > IF
>> > statement. Only, I can't figure one out that would work.
>> >
>> > Below is the data as I have it set up on each clients worksheet.
>> >
>> > Date Amount Cr.amt Date Amount Check #
>> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
>> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
>> > 8/1/2005 25.00 25.00
>> > 9/1/2005 25.00 25.00
>> > 10/1/2005 25.00
>> >
>> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
>> > background.
>> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
>> > formatting-
>> > Column A is pink if before Now and COlumn C formatted if amount is
>> > under
>> > $25.00.
>> > Row 5 is all white, therefore this client is up-to-date on payments.
>> >
>> > This works, but I can't see a way to show on the summary page that the
>> > account is overdue or not. Any help is appreciated! Thanks, Tara
>> >
>> >
>> > "FSt1" wrote:
>> >
>> >> hi,
>> >> forgot to mention.
>> >> highlight the a column and conditional fomat the whole column.
>> >>
>> >> regards
>> >> FSt1
>> >>
>> >> "FSt1" wrote:
>> >>
>> >> > hi,
>> >> > in column E you could put
>> >> >
>> >> > =if(A2<Now(), "Overdue","")
>> >> >
>> >> > or you could use conditional formating.
>> >> > go Format>conditional format
>> >> > enter Cell value is less than =now()
>> >> >
>> >> > then click the format button>patterns. pick a bright color to high
>> >> > light
>> >> > the overdue cell.
>> >> >
>> >> > regards
>> >> > FSt1
>> >> >
>> >> > "workin4alivin" wrote:
>> >> >
>> >> > > I want to be able to put in a formula that will tell me if a loan
>> >> > > recipient's
>> >> > > loan is past due.
>> >> > >
>> >> > > Column A is payment due date
>> >> > > Column B is payment amount due
>> >> > > Column D is acutal date payment is rec'd
>> >> > > Column E is actual amount received
>> >> > >
>> >> > > Any help that anyone can offer will be appreciated, including a
>> >> > > formula that
>> >> > > will work or a different way to set up my workbook to give me what
>> >> > > I
>> >> > > want.
>> >> > > (Essentially, I have a page for each borrower and a summary sheet
>> >> > > on
>> >> > > the
>> >> > > front page that shows original loan balance, balance due, late
>> >> > > payments,
>> >> > > etc.)
>> >> > >
>> >> > > Thank you, Tara
>>
>>
>>

Biff,

Here's the data that is on my summary page,

Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
$1,500 $1,225 7/1/2005 $140
$2,000 $1,915 7/3/2005 $85
$2,500 $600 7/19/2005 $100
$2,500 $2,000 8/10/2005 $200
$1,000 $925 7/6/2005 $75
$2,400 $200 7/18/2005 $100
$1,000 $530 7/5/2005 $50
$1,000 $622 8/5/2005 $42
$3,000 $2,916 7/1/2005 $84
$820 $495 7/29/2005 $100

I want the column listed Past Due 30 and Past Due 60 to have something
automatically entered or formated somehow to show when they are past due 30
or 60 days, based on today's date and information posted under each borrowers
page as the payments come in.

Essentially, I want a formula or something that says: If there isn't a
payment made on Account #1, Sheet 2, more recently than 30 days ago, the cell
under Past Due 30 days will show Red or Past Due or something.

THe data that is on sheet 2 is below with column headings.

Date Amount Cr. Amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
12/1/2004 25.00 25.00 1/14/2005 ($50) 980
4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
6/1/2005 25.00 25.00
7/1/2005 25.00 25.00
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

As you can see, this borrower is not past due. The last payment was 7/29,
but he paid his September payment too. He will not be past due until
11/01/05.
Below is a borrower that is behind:
Date Amount Date Amount
5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
6/13/2005 42.00 ($8.00)
7/13/2005 42.00
8/13/2005 42.00
9/13/2005 42.00

This borrower is behind. the last payment was $50 but it only covered 5/13
and part of 6/13. On the Summary page, I want it to show that this borrower
is past due 30 days. On Monday, she will be past due 60, so when I open it
MOnday, I want it to read 60 days.

I am not even sure that this is possible because there are so many
variables, thanks for taking the time to look at it though. Please don't
spend too much time working on it, though. If nothing jumps out at you, it's
not a big deal to look at each page.

Tara

"Biff" wrote:

> Hi!
>
> Sorry, I can't figure out what you're trying to do here. I copied the sample
> data you posted into a sheet but I still can't make heads or tails out it!
>
> Biff
>
> "workin4alivin" <[email protected]> wrote in message
> news:[email protected]...
> > FSt1 and Biff,
> >
> > Both of your suggestions worked. But my problem now is getting this
> > information to show on the summary sheet. I want a column that shows
> > whether
> > an account is overdue or not, which could allow me to link a cell on the
> > summary page to each client loan page.
> >
> > I am no expert in Excel, but I was thinking I needed to do some type of IF
> > statement. Only, I can't figure one out that would work.
> >
> > Below is the data as I have it set up on each clients worksheet.
> >
> > Date Amount Cr.amt Date Amount Check #
> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> > 8/1/2005 25.00 25.00
> > 9/1/2005 25.00 25.00
> > 10/1/2005 25.00
> >
> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> > background.
> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> > formatting-
> > Column A is pink if before Now and COlumn C formatted if amount is under
> > $25.00.
> > Row 5 is all white, therefore this client is up-to-date on payments.
> >
> > This works, but I can't see a way to show on the summary page that the
> > account is overdue or not. Any help is appreciated! Thanks, Tara
> >
> >
> > "FSt1" wrote:
> >
> >> hi,
> >> forgot to mention.
> >> highlight the a column and conditional fomat the whole column.
> >>
> >> regards
> >> FSt1
> >>
> >> "FSt1" wrote:
> >>
> >> > hi,
> >> > in column E you could put
> >> >
> >> > =if(A2<Now(), "Overdue","")
> >> >
> >> > or you could use conditional formating.
> >> > go Format>conditional format
> >> > enter Cell value is less than =now()
> >> >
> >> > then click the format button>patterns. pick a bright color to high
> >> > light
> >> > the overdue cell.
> >> >
> >> > regards
> >> > FSt1
> >> >
> >> > "workin4alivin" wrote:
> >> >
> >> > > I want to be able to put in a formula that will tell me if a loan
> >> > > recipient's
> >> > > loan is past due.
> >> > >
> >> > > Column A is payment due date
> >> > > Column B is payment amount due
> >> > > Column D is acutal date payment is rec'd
> >> > > Column E is actual amount received
> >> > >
> >> > > Any help that anyone can offer will be appreciated, including a
> >> > > formula that
> >> > > will work or a different way to set up my workbook to give me what I
> >> > > want.
> >> > > (Essentially, I have a page for each borrower and a summary sheet on
> >> > > the
> >> > > front page that shows original loan balance, balance due, late
> >> > > payments,
> >> > > etc.)
> >> > >
> >> > > Thank you, Tara
>
>
>

hi,
in column E you could put

=if(A2<Now(), "Overdue","")

or you could use conditional formating.
go Format>conditional format
enter Cell value is less than =now()

then click the format button>patterns. pick a bright color to high light
the overdue cell.

regards
FSt1

"workin4alivin" wrote:

> I want to be able to put in a formula that will tell me if a loan recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

Hi!

Sorry, I can't figure out what you're trying to do here. I copied the sample
data you posted into a sheet but I still can't make heads or tails out it!

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> FSt1 and Biff,
>
> Both of your suggestions worked. But my problem now is getting this
> information to show on the summary sheet. I want a column that shows
> whether
> an account is overdue or not, which could allow me to link a cell on the
> summary page to each client loan page.
>
> I am no expert in Excel, but I was thinking I needed to do some type of IF
> statement. Only, I can't figure one out that would work.
>
> Below is the data as I have it set up on each clients worksheet.
>
> Date Amount Cr.amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> background.
> Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> formatting-
> Column A is pink if before Now and COlumn C formatted if amount is under
> $25.00.
> Row 5 is all white, therefore this client is up-to-date on payments.
>
> This works, but I can't see a way to show on the summary page that the
> account is overdue or not. Any help is appreciated! Thanks, Tara
>
>
> "FSt1" wrote:
>
>> hi,
>> forgot to mention.
>> highlight the a column and conditional fomat the whole column.
>>
>> regards
>> FSt1
>>
>> "FSt1" wrote:
>>
>> > hi,
>> > in column E you could put
>> >
>> > =if(A2<Now(), "Overdue","")
>> >
>> > or you could use conditional formating.
>> > go Format>conditional format
>> > enter Cell value is less than =now()
>> >
>> > then click the format button>patterns. pick a bright color to high
>> > light
>> > the overdue cell.
>> >
>> > regards
>> > FSt1
>> >
>> > "workin4alivin" wrote:
>> >
>> > > I want to be able to put in a formula that will tell me if a loan
>> > > recipient's
>> > > loan is past due.
>> > >
>> > > Column A is payment due date
>> > > Column B is payment amount due
>> > > Column D is acutal date payment is rec'd
>> > > Column E is actual amount received
>> > >
>> > > Any help that anyone can offer will be appreciated, including a
>> > > formula that
>> > > will work or a different way to set up my workbook to give me what I
>> > > want.
>> > > (Essentially, I have a page for each borrower and a summary sheet on
>> > > the
>> > > front page that shows original loan balance, balance due, late
>> > > payments,
>> > > etc.)
>> > >
>> > > Thank you, Tara

FSt1 and Biff,

Both of your suggestions worked. But my problem now is getting this
information to show on the summary sheet. I want a column that shows whether
an account is overdue or not, which could allow me to link a cell on the
summary page to each client loan page.

I am no expert in Excel, but I was thinking I needed to do some type of IF
statement. Only, I can't figure one out that would work.

Below is the data as I have it set up on each clients worksheet.

Date Amount Cr.amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
10/1/2004 25.00 25.00 9/18/2004 ($75) 914
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
background.
Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally formatting-
Column A is pink if before Now and COlumn C formatted if amount is under
$25.00.
Row 5 is all white, therefore this client is up-to-date on payments.

This works, but I can't see a way to show on the summary page that the
account is overdue or not. Any help is appreciated! Thanks, Tara


"FSt1" wrote:

> hi,
> forgot to mention.
> highlight the a column and conditional fomat the whole column.
>
> regards
> FSt1
>
> "FSt1" wrote:
>
> > hi,
> > in column E you could put
> >
> > =if(A2<Now(), "Overdue","")
> >
> > or you could use conditional formating.
> > go Format>conditional format
> > enter Cell value is less than =now()
> >
> > then click the format button>patterns. pick a bright color to high light
> > the overdue cell.
> >
> > regards
> > FSt1
> >
> > "workin4alivin" wrote:
> >
> > > I want to be able to put in a formula that will tell me if a loan recipient's
> > > loan is past due.
> > >
> > > Column A is payment due date
> > > Column B is payment amount due
> > > Column D is acutal date payment is rec'd
> > > Column E is actual amount received
> > >
> > > Any help that anyone can offer will be appreciated, including a formula that
> > > will work or a different way to set up my workbook to give me what I want.
> > > (Essentially, I have a page for each borrower and a summary sheet on the
> > > front page that shows original loan balance, balance due, late payments,
> > > etc.)
> > >
> > > Thank you, Tara

hi,
forgot to mention.
highlight the a column and conditional fomat the whole column.

regards
FSt1

"FSt1" wrote:

> hi,
> in column E you could put
>
> =if(A2<Now(), "Overdue","")
>
> or you could use conditional formating.
> go Format>conditional format
> enter Cell value is less than =now()
>
> then click the format button>patterns. pick a bright color to high light
> the overdue cell.
>
> regards
> FSt1
>
> "workin4alivin" wrote:
>
> > I want to be able to put in a formula that will tell me if a loan recipient's
> > loan is past due.
> >
> > Column A is payment due date
> > Column B is payment amount due
> > Column D is acutal date payment is rec'd
> > Column E is actual amount received
> >
> > Any help that anyone can offer will be appreciated, including a formula that
> > will work or a different way to set up my workbook to give me what I want.
> > (Essentially, I have a page for each borrower and a summary sheet on the
> > front page that shows original loan balance, balance due, late payments,
> > etc.)
> >
> > Thank you, Tara

Hi!

One way is to use conditional formatting to highlight the Due Date cell
based on today's date and whether or not a date has been entered in the
Payment Rec'd cell.

A1 = payment due date
D1 = acutal date payment is rec'd

Select cell A1
Goto Format>Conditional Formatting
Formula is: =AND(A1<>"",TODAY()>=A1,D1="")
Click the Format button and maybe select a background color
OK out

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
>I want to be able to put in a formula that will tell me if a loan
>recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula
> that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

Hi!

Sorry, I can't figure out what you're trying to do here. I copied the sample
data you posted into a sheet but I still can't make heads or tails out it!

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> FSt1 and Biff,
>
> Both of your suggestions worked. But my problem now is getting this
> information to show on the summary sheet. I want a column that shows
> whether
> an account is overdue or not, which could allow me to link a cell on the
> summary page to each client loan page.
>
> I am no expert in Excel, but I was thinking I needed to do some type of IF
> statement. Only, I can't figure one out that would work.
>
> Below is the data as I have it set up on each clients worksheet.
>
> Date Amount Cr.amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> background.
> Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> formatting-
> Column A is pink if before Now and COlumn C formatted if amount is under
> $25.00.
> Row 5 is all white, therefore this client is up-to-date on payments.
>
> This works, but I can't see a way to show on the summary page that the
> account is overdue or not. Any help is appreciated! Thanks, Tara
>
>
> "FSt1" wrote:
>
>> hi,
>> forgot to mention.
>> highlight the a column and conditional fomat the whole column.
>>
>> regards
>> FSt1
>>
>> "FSt1" wrote:
>>
>> > hi,
>> > in column E you could put
>> >
>> > =if(A2<Now(), "Overdue","")
>> >
>> > or you could use conditional formating.
>> > go Format>conditional format
>> > enter Cell value is less than =now()
>> >
>> > then click the format button>patterns. pick a bright color to high
>> > light
>> > the overdue cell.
>> >
>> > regards
>> > FSt1
>> >
>> > "workin4alivin" wrote:
>> >
>> > > I want to be able to put in a formula that will tell me if a loan
>> > > recipient's
>> > > loan is past due.
>> > >
>> > > Column A is payment due date
>> > > Column B is payment amount due
>> > > Column D is acutal date payment is rec'd
>> > > Column E is actual amount received
>> > >
>> > > Any help that anyone can offer will be appreciated, including a
>> > > formula that
>> > > will work or a different way to set up my workbook to give me what I
>> > > want.
>> > > (Essentially, I have a page for each borrower and a summary sheet on
>> > > the
>> > > front page that shows original loan balance, balance due, late
>> > > payments,
>> > > etc.)
>> > >
>> > > Thank you, Tara

Hi!

>I am not even sure that this is possible because there are so many
>variables

The thing about Excel is that just about anything IS possible. The problem
is whether "we" know how to make it work!

Right now, I'm in the "problem" phase on this one!

Seems to me (based on my experience with credit) that a payment is due
monthly. If I pay more than is due I still have to make a payment next month
and the excess payments are just taken off of the backend of the loan
period.

I'm certain that this could be done but it might require a different layout
of the data but that in itself may be more work than it's worth.

>If nothing jumps out at you, it's not a big deal to look at each page.

Nothing is jumping out at me!

I don't see any relation between any of the data on the Summary sheet and
the data on the other sheets.

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Here's the data that is on my summary page,
>
> Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
> $1,500 $1,225 7/1/2005 $140
> $2,000 $1,915 7/3/2005 $85
> $2,500 $600 7/19/2005 $100
> $2,500 $2,000 8/10/2005 $200
> $1,000 $925 7/6/2005 $75
> $2,400 $200 7/18/2005 $100
> $1,000 $530 7/5/2005 $50
> $1,000 $622 8/5/2005 $42
> $3,000 $2,916 7/1/2005 $84
> $820 $495 7/29/2005 $100
>
> I want the column listed Past Due 30 and Past Due 60 to have something
> automatically entered or formated somehow to show when they are past due
> 30
> or 60 days, based on today's date and information posted under each
> borrowers
> page as the payments come in.
>
> Essentially, I want a formula or something that says: If there isn't a
> payment made on Account #1, Sheet 2, more recently than 30 days ago, the
> cell
> under Past Due 30 days will show Red or Past Due or something.
>
> THe data that is on sheet 2 is below with column headings.
>
> Date Amount Cr. Amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 12/1/2004 25.00 25.00 1/14/2005 ($50) 980
> 4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
> 5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
> 6/1/2005 25.00 25.00
> 7/1/2005 25.00 25.00
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> As you can see, this borrower is not past due. The last payment was 7/29,
> but he paid his September payment too. He will not be past due until
> 11/01/05.
> Below is a borrower that is behind:
> Date Amount Date Amount
> 5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
> 6/13/2005 42.00 ($8.00)
> 7/13/2005 42.00
> 8/13/2005 42.00
> 9/13/2005 42.00
>
> This borrower is behind. the last payment was $50 but it only covered
> 5/13
> and part of 6/13. On the Summary page, I want it to show that this
> borrower
> is past due 30 days. On Monday, she will be past due 60, so when I open
> it
> MOnday, I want it to read 60 days.
>
> I am not even sure that this is possible because there are so many
> variables, thanks for taking the time to look at it though. Please don't
> spend too much time working on it, though. If nothing jumps out at you,
> it's
> not a big deal to look at each page.
>
> Tara
>
> "Biff" wrote:
>
>> Hi!
>>
>> Sorry, I can't figure out what you're trying to do here. I copied the
>> sample
>> data you posted into a sheet but I still can't make heads or tails out
>> it!
>>
>> Biff
>>
>> "workin4alivin" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > FSt1 and Biff,
>> >
>> > Both of your suggestions worked. But my problem now is getting this
>> > information to show on the summary sheet. I want a column that shows
>> > whether
>> > an account is overdue or not, which could allow me to link a cell on
>> > the
>> > summary page to each client loan page.
>> >
>> > I am no expert in Excel, but I was thinking I needed to do some type of
>> > IF
>> > statement. Only, I can't figure one out that would work.
>> >
>> > Below is the data as I have it set up on each clients worksheet.
>> >
>> > Date Amount Cr.amt Date Amount Check #
>> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
>> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
>> > 8/1/2005 25.00 25.00
>> > 9/1/2005 25.00 25.00
>> > 10/1/2005 25.00
>> >
>> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
>> > background.
>> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
>> > formatting-
>> > Column A is pink if before Now and COlumn C formatted if amount is
>> > under
>> > $25.00.
>> > Row 5 is all white, therefore this client is up-to-date on payments.
>> >
>> > This works, but I can't see a way to show on the summary page that the
>> > account is overdue or not. Any help is appreciated! Thanks, Tara
>> >
>> >
>> > "FSt1" wrote:
>> >
>> >> hi,
>> >> forgot to mention.
>> >> highlight the a column and conditional fomat the whole column.
>> >>
>> >> regards
>> >> FSt1
>> >>
>> >> "FSt1" wrote:
>> >>
>> >> > hi,
>> >> > in column E you could put
>> >> >
>> >> > =if(A2<Now(), "Overdue","")
>> >> >
>> >> > or you could use conditional formating.
>> >> > go Format>conditional format
>> >> > enter Cell value is less than =now()
>> >> >
>> >> > then click the format button>patterns. pick a bright color to high
>> >> > light
>> >> > the overdue cell.
>> >> >
>> >> > regards
>> >> > FSt1
>> >> >
>> >> > "workin4alivin" wrote:
>> >> >
>> >> > > I want to be able to put in a formula that will tell me if a loan
>> >> > > recipient's
>> >> > > loan is past due.
>> >> > >
>> >> > > Column A is payment due date
>> >> > > Column B is payment amount due
>> >> > > Column D is acutal date payment is rec'd
>> >> > > Column E is actual amount received
>> >> > >
>> >> > > Any help that anyone can offer will be appreciated, including a
>> >> > > formula that
>> >> > > will work or a different way to set up my workbook to give me what
>> >> > > I
>> >> > > want.
>> >> > > (Essentially, I have a page for each borrower and a summary sheet
>> >> > > on
>> >> > > the
>> >> > > front page that shows original loan balance, balance due, late
>> >> > > payments,
>> >> > > etc.)
>> >> > >
>> >> > > Thank you, Tara
>>
>>
>>

Biff,

Here's the data that is on my summary page,

Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
$1,500 $1,225 7/1/2005 $140
$2,000 $1,915 7/3/2005 $85
$2,500 $600 7/19/2005 $100
$2,500 $2,000 8/10/2005 $200
$1,000 $925 7/6/2005 $75
$2,400 $200 7/18/2005 $100
$1,000 $530 7/5/2005 $50
$1,000 $622 8/5/2005 $42
$3,000 $2,916 7/1/2005 $84
$820 $495 7/29/2005 $100

I want the column listed Past Due 30 and Past Due 60 to have something
automatically entered or formated somehow to show when they are past due 30
or 60 days, based on today's date and information posted under each borrowers
page as the payments come in.

Essentially, I want a formula or something that says: If there isn't a
payment made on Account #1, Sheet 2, more recently than 30 days ago, the cell
under Past Due 30 days will show Red or Past Due or something.

THe data that is on sheet 2 is below with column headings.

Date Amount Cr. Amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
12/1/2004 25.00 25.00 1/14/2005 ($50) 980
4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
6/1/2005 25.00 25.00
7/1/2005 25.00 25.00
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

As you can see, this borrower is not past due. The last payment was 7/29,
but he paid his September payment too. He will not be past due until
11/01/05.
Below is a borrower that is behind:
Date Amount Date Amount
5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
6/13/2005 42.00 ($8.00)
7/13/2005 42.00
8/13/2005 42.00
9/13/2005 42.00

This borrower is behind. the last payment was $50 but it only covered 5/13
and part of 6/13. On the Summary page, I want it to show that this borrower
is past due 30 days. On Monday, she will be past due 60, so when I open it
MOnday, I want it to read 60 days.

I am not even sure that this is possible because there are so many
variables, thanks for taking the time to look at it though. Please don't
spend too much time working on it, though. If nothing jumps out at you, it's
not a big deal to look at each page.

Tara

"Biff" wrote:

> Hi!
>
> Sorry, I can't figure out what you're trying to do here. I copied the sample
> data you posted into a sheet but I still can't make heads or tails out it!
>
> Biff
>
> "workin4alivin" <[email protected]> wrote in message
> news:[email protected]...
> > FSt1 and Biff,
> >
> > Both of your suggestions worked. But my problem now is getting this
> > information to show on the summary sheet. I want a column that shows
> > whether
> > an account is overdue or not, which could allow me to link a cell on the
> > summary page to each client loan page.
> >
> > I am no expert in Excel, but I was thinking I needed to do some type of IF
> > statement. Only, I can't figure one out that would work.
> >
> > Below is the data as I have it set up on each clients worksheet.
> >
> > Date Amount Cr.amt Date Amount Check #
> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> > 8/1/2005 25.00 25.00
> > 9/1/2005 25.00 25.00
> > 10/1/2005 25.00
> >
> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> > background.
> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> > formatting-
> > Column A is pink if before Now and COlumn C formatted if amount is under
> > $25.00.
> > Row 5 is all white, therefore this client is up-to-date on payments.
> >
> > This works, but I can't see a way to show on the summary page that the
> > account is overdue or not. Any help is appreciated! Thanks, Tara
> >
> >
> > "FSt1" wrote:
> >
> >> hi,
> >> forgot to mention.
> >> highlight the a column and conditional fomat the whole column.
> >>
> >> regards
> >> FSt1
> >>
> >> "FSt1" wrote:
> >>
> >> > hi,
> >> > in column E you could put
> >> >
> >> > =if(A2<Now(), "Overdue","")
> >> >
> >> > or you could use conditional formating.
> >> > go Format>conditional format
> >> > enter Cell value is less than =now()
> >> >
> >> > then click the format button>patterns. pick a bright color to high
> >> > light
> >> > the overdue cell.
> >> >
> >> > regards
> >> > FSt1
> >> >
> >> > "workin4alivin" wrote:
> >> >
> >> > > I want to be able to put in a formula that will tell me if a loan
> >> > > recipient's
> >> > > loan is past due.
> >> > >
> >> > > Column A is payment due date
> >> > > Column B is payment amount due
> >> > > Column D is acutal date payment is rec'd
> >> > > Column E is actual amount received
> >> > >
> >> > > Any help that anyone can offer will be appreciated, including a
> >> > > formula that
> >> > > will work or a different way to set up my workbook to give me what I
> >> > > want.
> >> > > (Essentially, I have a page for each borrower and a summary sheet on
> >> > > the
> >> > > front page that shows original loan balance, balance due, late
> >> > > payments,
> >> > > etc.)
> >> > >
> >> > > Thank you, Tara
>
>
>

Hi!

One way is to use conditional formatting to highlight the Due Date cell
based on today's date and whether or not a date has been entered in the
Payment Rec'd cell.

A1 = payment due date
D1 = acutal date payment is rec'd

Select cell A1
Goto Format>Conditional Formatting
Formula is: =AND(A1<>"",TODAY()>=A1,D1="")
Click the Format button and maybe select a background color
OK out

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
>I want to be able to put in a formula that will tell me if a loan
>recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula
> that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

FSt1 and Biff,

Both of your suggestions worked. But my problem now is getting this
information to show on the summary sheet. I want a column that shows whether
an account is overdue or not, which could allow me to link a cell on the
summary page to each client loan page.

I am no expert in Excel, but I was thinking I needed to do some type of IF
statement. Only, I can't figure one out that would work.

Below is the data as I have it set up on each clients worksheet.

Date Amount Cr.amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
10/1/2004 25.00 25.00 9/18/2004 ($75) 914
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
background.
Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally formatting-
Column A is pink if before Now and COlumn C formatted if amount is under
$25.00.
Row 5 is all white, therefore this client is up-to-date on payments.

This works, but I can't see a way to show on the summary page that the
account is overdue or not. Any help is appreciated! Thanks, Tara


"FSt1" wrote:

> hi,
> forgot to mention.
> highlight the a column and conditional fomat the whole column.
>
> regards
> FSt1
>
> "FSt1" wrote:
>
> > hi,
> > in column E you could put
> >
> > =if(A2<Now(), "Overdue","")
> >
> > or you could use conditional formating.
> > go Format>conditional format
> > enter Cell value is less than =now()
> >
> > then click the format button>patterns. pick a bright color to high light
> > the overdue cell.
> >
> > regards
> > FSt1
> >
> > "workin4alivin" wrote:
> >
> > > I want to be able to put in a formula that will tell me if a loan recipient's
> > > loan is past due.
> > >
> > > Column A is payment due date
> > > Column B is payment amount due
> > > Column D is acutal date payment is rec'd
> > > Column E is actual amount received
> > >
> > > Any help that anyone can offer will be appreciated, including a formula that
> > > will work or a different way to set up my workbook to give me what I want.
> > > (Essentially, I have a page for each borrower and a summary sheet on the
> > > front page that shows original loan balance, balance due, late payments,
> > > etc.)
> > >
> > > Thank you, Tara

hi,
forgot to mention.
highlight the a column and conditional fomat the whole column.

regards
FSt1

"FSt1" wrote:

> hi,
> in column E you could put
>
> =if(A2<Now(), "Overdue","")
>
> or you could use conditional formating.
> go Format>conditional format
> enter Cell value is less than =now()
>
> then click the format button>patterns. pick a bright color to high light
> the overdue cell.
>
> regards
> FSt1
>
> "workin4alivin" wrote:
>
> > I want to be able to put in a formula that will tell me if a loan recipient's
> > loan is past due.
> >
> > Column A is payment due date
> > Column B is payment amount due
> > Column D is acutal date payment is rec'd
> > Column E is actual amount received
> >
> > Any help that anyone can offer will be appreciated, including a formula that
> > will work or a different way to set up my workbook to give me what I want.
> > (Essentially, I have a page for each borrower and a summary sheet on the
> > front page that shows original loan balance, balance due, late payments,
> > etc.)
> >
> > Thank you, Tara

hi,
in column E you could put

=if(A2<Now(), "Overdue","")

or you could use conditional formating.
go Format>conditional format
enter Cell value is less than =now()

then click the format button>patterns. pick a bright color to high light
the overdue cell.

regards
FSt1

"workin4alivin" wrote:

> I want to be able to put in a formula that will tell me if a loan recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

hi,
in column E you could put

=if(A2<Now(), "Overdue","")

or you could use conditional formating.
go Format>conditional format
enter Cell value is less than =now()

then click the format button>patterns. pick a bright color to high light
the overdue cell.

regards
FSt1

"workin4alivin" wrote:

> I want to be able to put in a formula that will tell me if a loan recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

Hi!

Sorry, I can't figure out what you're trying to do here. I copied the sample
data you posted into a sheet but I still can't make heads or tails out it!

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> FSt1 and Biff,
>
> Both of your suggestions worked. But my problem now is getting this
> information to show on the summary sheet. I want a column that shows
> whether
> an account is overdue or not, which could allow me to link a cell on the
> summary page to each client loan page.
>
> I am no expert in Excel, but I was thinking I needed to do some type of IF
> statement. Only, I can't figure one out that would work.
>
> Below is the data as I have it set up on each clients worksheet.
>
> Date Amount Cr.amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> background.
> Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> formatting-
> Column A is pink if before Now and COlumn C formatted if amount is under
> $25.00.
> Row 5 is all white, therefore this client is up-to-date on payments.
>
> This works, but I can't see a way to show on the summary page that the
> account is overdue or not. Any help is appreciated! Thanks, Tara
>
>
> "FSt1" wrote:
>
>> hi,
>> forgot to mention.
>> highlight the a column and conditional fomat the whole column.
>>
>> regards
>> FSt1
>>
>> "FSt1" wrote:
>>
>> > hi,
>> > in column E you could put
>> >
>> > =if(A2<Now(), "Overdue","")
>> >
>> > or you could use conditional formating.
>> > go Format>conditional format
>> > enter Cell value is less than =now()
>> >
>> > then click the format button>patterns. pick a bright color to high
>> > light
>> > the overdue cell.
>> >
>> > regards
>> > FSt1
>> >
>> > "workin4alivin" wrote:
>> >
>> > > I want to be able to put in a formula that will tell me if a loan
>> > > recipient's
>> > > loan is past due.
>> > >
>> > > Column A is payment due date
>> > > Column B is payment amount due
>> > > Column D is acutal date payment is rec'd
>> > > Column E is actual amount received
>> > >
>> > > Any help that anyone can offer will be appreciated, including a
>> > > formula that
>> > > will work or a different way to set up my workbook to give me what I
>> > > want.
>> > > (Essentially, I have a page for each borrower and a summary sheet on
>> > > the
>> > > front page that shows original loan balance, balance due, late
>> > > payments,
>> > > etc.)
>> > >
>> > > Thank you, Tara

FSt1 and Biff,

Both of your suggestions worked. But my problem now is getting this
information to show on the summary sheet. I want a column that shows whether
an account is overdue or not, which could allow me to link a cell on the
summary page to each client loan page.

I am no expert in Excel, but I was thinking I needed to do some type of IF
statement. Only, I can't figure one out that would work.

Below is the data as I have it set up on each clients worksheet.

Date Amount Cr.amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
10/1/2004 25.00 25.00 9/18/2004 ($75) 914
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
background.
Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally formatting-
Column A is pink if before Now and COlumn C formatted if amount is under
$25.00.
Row 5 is all white, therefore this client is up-to-date on payments.

This works, but I can't see a way to show on the summary page that the
account is overdue or not. Any help is appreciated! Thanks, Tara


"FSt1" wrote:

> hi,
> forgot to mention.
> highlight the a column and conditional fomat the whole column.
>
> regards
> FSt1
>
> "FSt1" wrote:
>
> > hi,
> > in column E you could put
> >
> > =if(A2<Now(), "Overdue","")
> >
> > or you could use conditional formating.
> > go Format>conditional format
> > enter Cell value is less than =now()
> >
> > then click the format button>patterns. pick a bright color to high light
> > the overdue cell.
> >
> > regards
> > FSt1
> >
> > "workin4alivin" wrote:
> >
> > > I want to be able to put in a formula that will tell me if a loan recipient's
> > > loan is past due.
> > >
> > > Column A is payment due date
> > > Column B is payment amount due
> > > Column D is acutal date payment is rec'd
> > > Column E is actual amount received
> > >
> > > Any help that anyone can offer will be appreciated, including a formula that
> > > will work or a different way to set up my workbook to give me what I want.
> > > (Essentially, I have a page for each borrower and a summary sheet on the
> > > front page that shows original loan balance, balance due, late payments,
> > > etc.)
> > >
> > > Thank you, Tara

Biff,

Here's the data that is on my summary page,

Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
$1,500 $1,225 7/1/2005 $140
$2,000 $1,915 7/3/2005 $85
$2,500 $600 7/19/2005 $100
$2,500 $2,000 8/10/2005 $200
$1,000 $925 7/6/2005 $75
$2,400 $200 7/18/2005 $100
$1,000 $530 7/5/2005 $50
$1,000 $622 8/5/2005 $42
$3,000 $2,916 7/1/2005 $84
$820 $495 7/29/2005 $100

I want the column listed Past Due 30 and Past Due 60 to have something
automatically entered or formated somehow to show when they are past due 30
or 60 days, based on today's date and information posted under each borrowers
page as the payments come in.

Essentially, I want a formula or something that says: If there isn't a
payment made on Account #1, Sheet 2, more recently than 30 days ago, the cell
under Past Due 30 days will show Red or Past Due or something.

THe data that is on sheet 2 is below with column headings.

Date Amount Cr. Amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
12/1/2004 25.00 25.00 1/14/2005 ($50) 980
4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
6/1/2005 25.00 25.00
7/1/2005 25.00 25.00
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

As you can see, this borrower is not past due. The last payment was 7/29,
but he paid his September payment too. He will not be past due until
11/01/05.
Below is a borrower that is behind:
Date Amount Date Amount
5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
6/13/2005 42.00 ($8.00)
7/13/2005 42.00
8/13/2005 42.00
9/13/2005 42.00

This borrower is behind. the last payment was $50 but it only covered 5/13
and part of 6/13. On the Summary page, I want it to show that this borrower
is past due 30 days. On Monday, she will be past due 60, so when I open it
MOnday, I want it to read 60 days.

I am not even sure that this is possible because there are so many
variables, thanks for taking the time to look at it though. Please don't
spend too much time working on it, though. If nothing jumps out at you, it's
not a big deal to look at each page.

Tara

"Biff" wrote:

> Hi!
>
> Sorry, I can't figure out what you're trying to do here. I copied the sample
> data you posted into a sheet but I still can't make heads or tails out it!
>
> Biff
>
> "workin4alivin" <[email protected]> wrote in message
> news:[email protected]...
> > FSt1 and Biff,
> >
> > Both of your suggestions worked. But my problem now is getting this
> > information to show on the summary sheet. I want a column that shows
> > whether
> > an account is overdue or not, which could allow me to link a cell on the
> > summary page to each client loan page.
> >
> > I am no expert in Excel, but I was thinking I needed to do some type of IF
> > statement. Only, I can't figure one out that would work.
> >
> > Below is the data as I have it set up on each clients worksheet.
> >
> > Date Amount Cr.amt Date Amount Check #
> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> > 8/1/2005 25.00 25.00
> > 9/1/2005 25.00 25.00
> > 10/1/2005 25.00
> >
> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> > background.
> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> > formatting-
> > Column A is pink if before Now and COlumn C formatted if amount is under
> > $25.00.
> > Row 5 is all white, therefore this client is up-to-date on payments.
> >
> > This works, but I can't see a way to show on the summary page that the
> > account is overdue or not. Any help is appreciated! Thanks, Tara
> >
> >
> > "FSt1" wrote:
> >
> >> hi,
> >> forgot to mention.
> >> highlight the a column and conditional fomat the whole column.
> >>
> >> regards
> >> FSt1
> >>
> >> "FSt1" wrote:
> >>
> >> > hi,
> >> > in column E you could put
> >> >
> >> > =if(A2<Now(), "Overdue","")
> >> >
> >> > or you could use conditional formating.
> >> > go Format>conditional format
> >> > enter Cell value is less than =now()
> >> >
> >> > then click the format button>patterns. pick a bright color to high
> >> > light
> >> > the overdue cell.
> >> >
> >> > regards
> >> > FSt1
> >> >
> >> > "workin4alivin" wrote:
> >> >
> >> > > I want to be able to put in a formula that will tell me if a loan
> >> > > recipient's
> >> > > loan is past due.
> >> > >
> >> > > Column A is payment due date
> >> > > Column B is payment amount due
> >> > > Column D is acutal date payment is rec'd
> >> > > Column E is actual amount received
> >> > >
> >> > > Any help that anyone can offer will be appreciated, including a
> >> > > formula that
> >> > > will work or a different way to set up my workbook to give me what I
> >> > > want.
> >> > > (Essentially, I have a page for each borrower and a summary sheet on
> >> > > the
> >> > > front page that shows original loan balance, balance due, late
> >> > > payments,
> >> > > etc.)
> >> > >
> >> > > Thank you, Tara
>
>
>

Hi!

>I am not even sure that this is possible because there are so many
>variables

The thing about Excel is that just about anything IS possible. The problem
is whether "we" know how to make it work!

Right now, I'm in the "problem" phase on this one!

Seems to me (based on my experience with credit) that a payment is due
monthly. If I pay more than is due I still have to make a payment next month
and the excess payments are just taken off of the backend of the loan
period.

I'm certain that this could be done but it might require a different layout
of the data but that in itself may be more work than it's worth.

>If nothing jumps out at you, it's not a big deal to look at each page.

Nothing is jumping out at me!

I don't see any relation between any of the data on the Summary sheet and
the data on the other sheets.

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Here's the data that is on my summary page,
>
> Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
> $1,500 $1,225 7/1/2005 $140
> $2,000 $1,915 7/3/2005 $85
> $2,500 $600 7/19/2005 $100
> $2,500 $2,000 8/10/2005 $200
> $1,000 $925 7/6/2005 $75
> $2,400 $200 7/18/2005 $100
> $1,000 $530 7/5/2005 $50
> $1,000 $622 8/5/2005 $42
> $3,000 $2,916 7/1/2005 $84
> $820 $495 7/29/2005 $100
>
> I want the column listed Past Due 30 and Past Due 60 to have something
> automatically entered or formated somehow to show when they are past due
> 30
> or 60 days, based on today's date and information posted under each
> borrowers
> page as the payments come in.
>
> Essentially, I want a formula or something that says: If there isn't a
> payment made on Account #1, Sheet 2, more recently than 30 days ago, the
> cell
> under Past Due 30 days will show Red or Past Due or something.
>
> THe data that is on sheet 2 is below with column headings.
>
> Date Amount Cr. Amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 12/1/2004 25.00 25.00 1/14/2005 ($50) 980
> 4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
> 5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
> 6/1/2005 25.00 25.00
> 7/1/2005 25.00 25.00
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> As you can see, this borrower is not past due. The last payment was 7/29,
> but he paid his September payment too. He will not be past due until
> 11/01/05.
> Below is a borrower that is behind:
> Date Amount Date Amount
> 5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
> 6/13/2005 42.00 ($8.00)
> 7/13/2005 42.00
> 8/13/2005 42.00
> 9/13/2005 42.00
>
> This borrower is behind. the last payment was $50 but it only covered
> 5/13
> and part of 6/13. On the Summary page, I want it to show that this
> borrower
> is past due 30 days. On Monday, she will be past due 60, so when I open
> it
> MOnday, I want it to read 60 days.
>
> I am not even sure that this is possible because there are so many
> variables, thanks for taking the time to look at it though. Please don't
> spend too much time working on it, though. If nothing jumps out at you,
> it's
> not a big deal to look at each page.
>
> Tara
>
> "Biff" wrote:
>
>> Hi!
>>
>> Sorry, I can't figure out what you're trying to do here. I copied the
>> sample
>> data you posted into a sheet but I still can't make heads or tails out
>> it!
>>
>> Biff
>>
>> "workin4alivin" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > FSt1 and Biff,
>> >
>> > Both of your suggestions worked. But my problem now is getting this
>> > information to show on the summary sheet. I want a column that shows
>> > whether
>> > an account is overdue or not, which could allow me to link a cell on
>> > the
>> > summary page to each client loan page.
>> >
>> > I am no expert in Excel, but I was thinking I needed to do some type of
>> > IF
>> > statement. Only, I can't figure one out that would work.
>> >
>> > Below is the data as I have it set up on each clients worksheet.
>> >
>> > Date Amount Cr.amt Date Amount Check #
>> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
>> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
>> > 8/1/2005 25.00 25.00
>> > 9/1/2005 25.00 25.00
>> > 10/1/2005 25.00
>> >
>> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
>> > background.
>> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
>> > formatting-
>> > Column A is pink if before Now and COlumn C formatted if amount is
>> > under
>> > $25.00.
>> > Row 5 is all white, therefore this client is up-to-date on payments.
>> >
>> > This works, but I can't see a way to show on the summary page that the
>> > account is overdue or not. Any help is appreciated! Thanks, Tara
>> >
>> >
>> > "FSt1" wrote:
>> >
>> >> hi,
>> >> forgot to mention.
>> >> highlight the a column and conditional fomat the whole column.
>> >>
>> >> regards
>> >> FSt1
>> >>
>> >> "FSt1" wrote:
>> >>
>> >> > hi,
>> >> > in column E you could put
>> >> >
>> >> > =if(A2<Now(), "Overdue","")
>> >> >
>> >> > or you could use conditional formating.
>> >> > go Format>conditional format
>> >> > enter Cell value is less than =now()
>> >> >
>> >> > then click the format button>patterns. pick a bright color to high
>> >> > light
>> >> > the overdue cell.
>> >> >
>> >> > regards
>> >> > FSt1
>> >> >
>> >> > "workin4alivin" wrote:
>> >> >
>> >> > > I want to be able to put in a formula that will tell me if a loan
>> >> > > recipient's
>> >> > > loan is past due.
>> >> > >
>> >> > > Column A is payment due date
>> >> > > Column B is payment amount due
>> >> > > Column D is acutal date payment is rec'd
>> >> > > Column E is actual amount received
>> >> > >
>> >> > > Any help that anyone can offer will be appreciated, including a
>> >> > > formula that
>> >> > > will work or a different way to set up my workbook to give me what
>> >> > > I
>> >> > > want.
>> >> > > (Essentially, I have a page for each borrower and a summary sheet
>> >> > > on
>> >> > > the
>> >> > > front page that shows original loan balance, balance due, late
>> >> > > payments,
>> >> > > etc.)
>> >> > >
>> >> > > Thank you, Tara
>>
>>
>>

hi,
forgot to mention.
highlight the a column and conditional fomat the whole column.

regards
FSt1

"FSt1" wrote:

> hi,
> in column E you could put
>
> =if(A2<Now(), "Overdue","")
>
> or you could use conditional formating.
> go Format>conditional format
> enter Cell value is less than =now()
>
> then click the format button>patterns. pick a bright color to high light
> the overdue cell.
>
> regards
> FSt1
>
> "workin4alivin" wrote:
>
> > I want to be able to put in a formula that will tell me if a loan recipient's
> > loan is past due.
> >
> > Column A is payment due date
> > Column B is payment amount due
> > Column D is acutal date payment is rec'd
> > Column E is actual amount received
> >
> > Any help that anyone can offer will be appreciated, including a formula that
> > will work or a different way to set up my workbook to give me what I want.
> > (Essentially, I have a page for each borrower and a summary sheet on the
> > front page that shows original loan balance, balance due, late payments,
> > etc.)
> >
> > Thank you, Tara

Hi!

One way is to use conditional formatting to highlight the Due Date cell
based on today's date and whether or not a date has been entered in the
Payment Rec'd cell.

A1 = payment due date
D1 = acutal date payment is rec'd

Select cell A1
Goto Format>Conditional Formatting
Formula is: =AND(A1<>"",TODAY()>=A1,D1="")
Click the Format button and maybe select a background color
OK out

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
>I want to be able to put in a formula that will tell me if a loan
>recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula
> that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

Hi!

Sorry, I can't figure out what you're trying to do here. I copied the sample
data you posted into a sheet but I still can't make heads or tails out it!

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> FSt1 and Biff,
>
> Both of your suggestions worked. But my problem now is getting this
> information to show on the summary sheet. I want a column that shows
> whether
> an account is overdue or not, which could allow me to link a cell on the
> summary page to each client loan page.
>
> I am no expert in Excel, but I was thinking I needed to do some type of IF
> statement. Only, I can't figure one out that would work.
>
> Below is the data as I have it set up on each clients worksheet.
>
> Date Amount Cr.amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> background.
> Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> formatting-
> Column A is pink if before Now and COlumn C formatted if amount is under
> $25.00.
> Row 5 is all white, therefore this client is up-to-date on payments.
>
> This works, but I can't see a way to show on the summary page that the
> account is overdue or not. Any help is appreciated! Thanks, Tara
>
>
> "FSt1" wrote:
>
>> hi,
>> forgot to mention.
>> highlight the a column and conditional fomat the whole column.
>>
>> regards
>> FSt1
>>
>> "FSt1" wrote:
>>
>> > hi,
>> > in column E you could put
>> >
>> > =if(A2<Now(), "Overdue","")
>> >
>> > or you could use conditional formating.
>> > go Format>conditional format
>> > enter Cell value is less than =now()
>> >
>> > then click the format button>patterns. pick a bright color to high
>> > light
>> > the overdue cell.
>> >
>> > regards
>> > FSt1
>> >
>> > "workin4alivin" wrote:
>> >
>> > > I want to be able to put in a formula that will tell me if a loan
>> > > recipient's
>> > > loan is past due.
>> > >
>> > > Column A is payment due date
>> > > Column B is payment amount due
>> > > Column D is acutal date payment is rec'd
>> > > Column E is actual amount received
>> > >
>> > > Any help that anyone can offer will be appreciated, including a
>> > > formula that
>> > > will work or a different way to set up my workbook to give me what I
>> > > want.
>> > > (Essentially, I have a page for each borrower and a summary sheet on
>> > > the
>> > > front page that shows original loan balance, balance due, late
>> > > payments,
>> > > etc.)
>> > >
>> > > Thank you, Tara

Hi!

>I am not even sure that this is possible because there are so many
>variables

The thing about Excel is that just about anything IS possible. The problem
is whether "we" know how to make it work!

Right now, I'm in the "problem" phase on this one!

Seems to me (based on my experience with credit) that a payment is due
monthly. If I pay more than is due I still have to make a payment next month
and the excess payments are just taken off of the backend of the loan
period.

I'm certain that this could be done but it might require a different layout
of the data but that in itself may be more work than it's worth.

>If nothing jumps out at you, it's not a big deal to look at each page.

Nothing is jumping out at me!

I don't see any relation between any of the data on the Summary sheet and
the data on the other sheets.

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Here's the data that is on my summary page,
>
> Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
> $1,500 $1,225 7/1/2005 $140
> $2,000 $1,915 7/3/2005 $85
> $2,500 $600 7/19/2005 $100
> $2,500 $2,000 8/10/2005 $200
> $1,000 $925 7/6/2005 $75
> $2,400 $200 7/18/2005 $100
> $1,000 $530 7/5/2005 $50
> $1,000 $622 8/5/2005 $42
> $3,000 $2,916 7/1/2005 $84
> $820 $495 7/29/2005 $100
>
> I want the column listed Past Due 30 and Past Due 60 to have something
> automatically entered or formated somehow to show when they are past due
> 30
> or 60 days, based on today's date and information posted under each
> borrowers
> page as the payments come in.
>
> Essentially, I want a formula or something that says: If there isn't a
> payment made on Account #1, Sheet 2, more recently than 30 days ago, the
> cell
> under Past Due 30 days will show Red or Past Due or something.
>
> THe data that is on sheet 2 is below with column headings.
>
> Date Amount Cr. Amt Date Amount Check #
> 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> 12/1/2004 25.00 25.00 1/14/2005 ($50) 980
> 4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
> 5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
> 6/1/2005 25.00 25.00
> 7/1/2005 25.00 25.00
> 8/1/2005 25.00 25.00
> 9/1/2005 25.00 25.00
> 10/1/2005 25.00
>
> As you can see, this borrower is not past due. The last payment was 7/29,
> but he paid his September payment too. He will not be past due until
> 11/01/05.
> Below is a borrower that is behind:
> Date Amount Date Amount
> 5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
> 6/13/2005 42.00 ($8.00)
> 7/13/2005 42.00
> 8/13/2005 42.00
> 9/13/2005 42.00
>
> This borrower is behind. the last payment was $50 but it only covered
> 5/13
> and part of 6/13. On the Summary page, I want it to show that this
> borrower
> is past due 30 days. On Monday, she will be past due 60, so when I open
> it
> MOnday, I want it to read 60 days.
>
> I am not even sure that this is possible because there are so many
> variables, thanks for taking the time to look at it though. Please don't
> spend too much time working on it, though. If nothing jumps out at you,
> it's
> not a big deal to look at each page.
>
> Tara
>
> "Biff" wrote:
>
>> Hi!
>>
>> Sorry, I can't figure out what you're trying to do here. I copied the
>> sample
>> data you posted into a sheet but I still can't make heads or tails out
>> it!
>>
>> Biff
>>
>> "workin4alivin" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > FSt1 and Biff,
>> >
>> > Both of your suggestions worked. But my problem now is getting this
>> > information to show on the summary sheet. I want a column that shows
>> > whether
>> > an account is overdue or not, which could allow me to link a cell on
>> > the
>> > summary page to each client loan page.
>> >
>> > I am no expert in Excel, but I was thinking I needed to do some type of
>> > IF
>> > statement. Only, I can't figure one out that would work.
>> >
>> > Below is the data as I have it set up on each clients worksheet.
>> >
>> > Date Amount Cr.amt Date Amount Check #
>> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
>> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
>> > 8/1/2005 25.00 25.00
>> > 9/1/2005 25.00 25.00
>> > 10/1/2005 25.00
>> >
>> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
>> > background.
>> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
>> > formatting-
>> > Column A is pink if before Now and COlumn C formatted if amount is
>> > under
>> > $25.00.
>> > Row 5 is all white, therefore this client is up-to-date on payments.
>> >
>> > This works, but I can't see a way to show on the summary page that the
>> > account is overdue or not. Any help is appreciated! Thanks, Tara
>> >
>> >
>> > "FSt1" wrote:
>> >
>> >> hi,
>> >> forgot to mention.
>> >> highlight the a column and conditional fomat the whole column.
>> >>
>> >> regards
>> >> FSt1
>> >>
>> >> "FSt1" wrote:
>> >>
>> >> > hi,
>> >> > in column E you could put
>> >> >
>> >> > =if(A2<Now(), "Overdue","")
>> >> >
>> >> > or you could use conditional formating.
>> >> > go Format>conditional format
>> >> > enter Cell value is less than =now()
>> >> >
>> >> > then click the format button>patterns. pick a bright color to high
>> >> > light
>> >> > the overdue cell.
>> >> >
>> >> > regards
>> >> > FSt1
>> >> >
>> >> > "workin4alivin" wrote:
>> >> >
>> >> > > I want to be able to put in a formula that will tell me if a loan
>> >> > > recipient's
>> >> > > loan is past due.
>> >> > >
>> >> > > Column A is payment due date
>> >> > > Column B is payment amount due
>> >> > > Column D is acutal date payment is rec'd
>> >> > > Column E is actual amount received
>> >> > >
>> >> > > Any help that anyone can offer will be appreciated, including a
>> >> > > formula that
>> >> > > will work or a different way to set up my workbook to give me what
>> >> > > I
>> >> > > want.
>> >> > > (Essentially, I have a page for each borrower and a summary sheet
>> >> > > on
>> >> > > the
>> >> > > front page that shows original loan balance, balance due, late
>> >> > > payments,
>> >> > > etc.)
>> >> > >
>> >> > > Thank you, Tara
>>
>>
>>

Biff,

Here's the data that is on my summary page,

Ln Amt Ln Bal Lst pymt Last pymt amt Past Due 30 Past Due 60
$1,500 $1,225 7/1/2005 $140
$2,000 $1,915 7/3/2005 $85
$2,500 $600 7/19/2005 $100
$2,500 $2,000 8/10/2005 $200
$1,000 $925 7/6/2005 $75
$2,400 $200 7/18/2005 $100
$1,000 $530 7/5/2005 $50
$1,000 $622 8/5/2005 $42
$3,000 $2,916 7/1/2005 $84
$820 $495 7/29/2005 $100

I want the column listed Past Due 30 and Past Due 60 to have something
automatically entered or formated somehow to show when they are past due 30
or 60 days, based on today's date and information posted under each borrowers
page as the payments come in.

Essentially, I want a formula or something that says: If there isn't a
payment made on Account #1, Sheet 2, more recently than 30 days ago, the cell
under Past Due 30 days will show Red or Past Due or something.

THe data that is on sheet 2 is below with column headings.

Date Amount Cr. Amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
12/1/2004 25.00 25.00 1/14/2005 ($50) 980
4/1/2005 25.00 25.00 5/5/2005 ($25) 1032
5/1/2005 25.00 25.00 7/29/2005 ($100) 1070
6/1/2005 25.00 25.00
7/1/2005 25.00 25.00
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

As you can see, this borrower is not past due. The last payment was 7/29,
but he paid his September payment too. He will not be past due until
11/01/05.
Below is a borrower that is behind:
Date Amount Date Amount
5/13/2005 42.00 ($42.00) 7/5/2005 (50.00)
6/13/2005 42.00 ($8.00)
7/13/2005 42.00
8/13/2005 42.00
9/13/2005 42.00

This borrower is behind. the last payment was $50 but it only covered 5/13
and part of 6/13. On the Summary page, I want it to show that this borrower
is past due 30 days. On Monday, she will be past due 60, so when I open it
MOnday, I want it to read 60 days.

I am not even sure that this is possible because there are so many
variables, thanks for taking the time to look at it though. Please don't
spend too much time working on it, though. If nothing jumps out at you, it's
not a big deal to look at each page.

Tara

"Biff" wrote:

> Hi!
>
> Sorry, I can't figure out what you're trying to do here. I copied the sample
> data you posted into a sheet but I still can't make heads or tails out it!
>
> Biff
>
> "workin4alivin" <[email protected]> wrote in message
> news:[email protected]...
> > FSt1 and Biff,
> >
> > Both of your suggestions worked. But my problem now is getting this
> > information to show on the summary sheet. I want a column that shows
> > whether
> > an account is overdue or not, which could allow me to link a cell on the
> > summary page to each client loan page.
> >
> > I am no expert in Excel, but I was thinking I needed to do some type of IF
> > statement. Only, I can't figure one out that would work.
> >
> > Below is the data as I have it set up on each clients worksheet.
> >
> > Date Amount Cr.amt Date Amount Check #
> > 9/1/2004 25.00 25.00 8/31/2004 ($25) 902
> > 10/1/2004 25.00 25.00 9/18/2004 ($75) 914
> > 8/1/2005 25.00 25.00
> > 9/1/2005 25.00 25.00
> > 10/1/2005 25.00
> >
> > Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
> > background.
> > Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally
> > formatting-
> > Column A is pink if before Now and COlumn C formatted if amount is under
> > $25.00.
> > Row 5 is all white, therefore this client is up-to-date on payments.
> >
> > This works, but I can't see a way to show on the summary page that the
> > account is overdue or not. Any help is appreciated! Thanks, Tara
> >
> >
> > "FSt1" wrote:
> >
> >> hi,
> >> forgot to mention.
> >> highlight the a column and conditional fomat the whole column.
> >>
> >> regards
> >> FSt1
> >>
> >> "FSt1" wrote:
> >>
> >> > hi,
> >> > in column E you could put
> >> >
> >> > =if(A2<Now(), "Overdue","")
> >> >
> >> > or you could use conditional formating.
> >> > go Format>conditional format
> >> > enter Cell value is less than =now()
> >> >
> >> > then click the format button>patterns. pick a bright color to high
> >> > light
> >> > the overdue cell.
> >> >
> >> > regards
> >> > FSt1
> >> >
> >> > "workin4alivin" wrote:
> >> >
> >> > > I want to be able to put in a formula that will tell me if a loan
> >> > > recipient's
> >> > > loan is past due.
> >> > >
> >> > > Column A is payment due date
> >> > > Column B is payment amount due
> >> > > Column D is acutal date payment is rec'd
> >> > > Column E is actual amount received
> >> > >
> >> > > Any help that anyone can offer will be appreciated, including a
> >> > > formula that
> >> > > will work or a different way to set up my workbook to give me what I
> >> > > want.
> >> > > (Essentially, I have a page for each borrower and a summary sheet on
> >> > > the
> >> > > front page that shows original loan balance, balance due, late
> >> > > payments,
> >> > > etc.)
> >> > >
> >> > > Thank you, Tara
>
>
>

I want to be able to put in a formula that will tell me if a loan recipient's
loan is past due.

Column A is payment due date
Column B is payment amount due
Column D is acutal date payment is rec'd
Column E is actual amount received

Any help that anyone can offer will be appreciated, including a formula that
will work or a different way to set up my workbook to give me what I want.
(Essentially, I have a page for each borrower and a summary sheet on the
front page that shows original loan balance, balance due, late payments,
etc.)

Thank you, Tara

FSt1 and Biff,

Both of your suggestions worked. But my problem now is getting this
information to show on the summary sheet. I want a column that shows whether
an account is overdue or not, which could allow me to link a cell on the
summary page to each client loan page.

I am no expert in Excel, but I was thinking I needed to do some type of IF
statement. Only, I can't figure one out that would work.

Below is the data as I have it set up on each clients worksheet.

Date Amount Cr.amt Date Amount Check #
9/1/2004 25.00 25.00 8/31/2004 ($25) 902
10/1/2004 25.00 25.00 9/18/2004 ($75) 914
8/1/2005 25.00 25.00
9/1/2005 25.00 25.00
10/1/2005 25.00

Currently, cell A2, A3, A4 are pink background and A5 and A6 are white
background.
Cell C2, C3, C4 and C5 are blank and C6 is pink. (Conditionally formatting-
Column A is pink if before Now and COlumn C formatted if amount is under
$25.00.
Row 5 is all white, therefore this client is up-to-date on payments.

This works, but I can't see a way to show on the summary page that the
account is overdue or not. Any help is appreciated! Thanks, Tara


"FSt1" wrote:

> hi,
> forgot to mention.
> highlight the a column and conditional fomat the whole column.
>
> regards
> FSt1
>
> "FSt1" wrote:
>
> > hi,
> > in column E you could put
> >
> > =if(A2<Now(), "Overdue","")
> >
> > or you could use conditional formating.
> > go Format>conditional format
> > enter Cell value is less than =now()
> >
> > then click the format button>patterns. pick a bright color to high light
> > the overdue cell.
> >
> > regards
> > FSt1
> >
> > "workin4alivin" wrote:
> >
> > > I want to be able to put in a formula that will tell me if a loan recipient's
> > > loan is past due.
> > >
> > > Column A is payment due date
> > > Column B is payment amount due
> > > Column D is acutal date payment is rec'd
> > > Column E is actual amount received
> > >
> > > Any help that anyone can offer will be appreciated, including a formula that
> > > will work or a different way to set up my workbook to give me what I want.
> > > (Essentially, I have a page for each borrower and a summary sheet on the
> > > front page that shows original loan balance, balance due, late payments,
> > > etc.)
> > >
> > > Thank you, Tara

hi,
forgot to mention.
highlight the a column and conditional fomat the whole column.

regards
FSt1

"FSt1" wrote:

> hi,
> in column E you could put
>
> =if(A2<Now(), "Overdue","")
>
> or you could use conditional formating.
> go Format>conditional format
> enter Cell value is less than =now()
>
> then click the format button>patterns. pick a bright color to high light
> the overdue cell.
>
> regards
> FSt1
>
> "workin4alivin" wrote:
>
> > I want to be able to put in a formula that will tell me if a loan recipient's
> > loan is past due.
> >
> > Column A is payment due date
> > Column B is payment amount due
> > Column D is acutal date payment is rec'd
> > Column E is actual amount received
> >
> > Any help that anyone can offer will be appreciated, including a formula that
> > will work or a different way to set up my workbook to give me what I want.
> > (Essentially, I have a page for each borrower and a summary sheet on the
> > front page that shows original loan balance, balance due, late payments,
> > etc.)
> >
> > Thank you, Tara

Hi!

One way is to use conditional formatting to highlight the Due Date cell
based on today's date and whether or not a date has been entered in the
Payment Rec'd cell.

A1 = payment due date
D1 = acutal date payment is rec'd

Select cell A1
Goto Format>Conditional Formatting
Formula is: =AND(A1<>"",TODAY()>=A1,D1="")
Click the Format button and maybe select a background color
OK out

Biff

"workin4alivin" <[email protected]> wrote in message
news:[email protected]...
>I want to be able to put in a formula that will tell me if a loan
>recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula
> that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara

hi,
in column E you could put

=if(A2<Now(), "Overdue","")

or you could use conditional formating.
go Format>conditional format
enter Cell value is less than =now()

then click the format button>patterns. pick a bright color to high light
the overdue cell.

regards
FSt1

"workin4alivin" wrote:

> I want to be able to put in a formula that will tell me if a loan recipient's
> loan is past due.
>
> Column A is payment due date
> Column B is payment amount due
> Column D is acutal date payment is rec'd
> Column E is actual amount received
>
> Any help that anyone can offer will be appreciated, including a formula that
> will work or a different way to set up my workbook to give me what I want.
> (Essentially, I have a page for each borrower and a summary sheet on the
> front page that shows original loan balance, balance due, late payments,
> etc.)
>
> Thank you, Tara