RE: worksheet function

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi,

I still have one more question. Is the late fee waived (or imposed) if a
partial payment is made on or before 11th? So, I am suggesting formulas for
both scenarios.

In a blank cell enter the month number (1 for Jan, .... 12 for Dec). I
have assumed the cell it to be A4 in my formulas. You can use any other cell
in the same row, eg., X4 or Y4 ....., that suits you instead, and
appropriately replace A4 by that in the formulas.

Scenario 1: Late fee is imposed unless payment is made IN FULL before 11th
of the month.
In J4 enter the formula
=IF(MONTH(D4)<A4,0,IF(MONTH(D4)=A4,IF(AND(DAY(D4)<11,C4>=E4),0,25),25))
In K4 enter the formula
=E4+J4-C4

Scenario 2: Late fee is waived if a partial payment is made before the 11th
of the month.
In J4 enter the formula
=IF(MONTH(D4)<A4,0,IF(MONTH(D4)=A4,IF(AND(DAY(D4)<11,C4>=1),0,25),25))
In K4 enter the formula
=E4+J4-C4
(if Scenario 2 is the case, and if you require a certain minimum payment
before 11th of the month to waive the late fee, for example, $50.00, modify
the J4 equation where C4>=1 ad C4>=50 (or whatever).

One more thing you should keep in mind: the formula wouldn't work for
December (i.e., if someone makes a payment due in December in January, the
formula would think that the payment was made on time. Since 1<12! I can
modify the formula to account for that also, but it would get even more
complex.
Hope this helps. Regards.

B. R. Ramachandran





"Nadji" wrote:

> Thanks so much for trying to help I know it's confusing. I work for a
> housing program and the tenants have been late with their rents so i'm trying
> to create something so I can track when they pay and if they are late to add
> on a late fee of $25. To answer each question see below:
>
> Yes if paid before due date C4= amt pd
> No, D4=date money was paid
> Yes, if payment is made 10 days after due date J4=late fee $25
> E4=rent amt (the amount that should be paid but some people pay some so
> there is always a balance due)
> Yes, K4= bal due if the amount due is paid after the 10th or if there is an
> amount that wasn't paid the previous month, or if the rent is paid but not
> the late fee. D4 just determines if J4 is added to bal due.
> yes
> Yes not a due date just the date it was paid. Ihope I havent further
> confused you thanks for all your efort in trying to help me, I am going crazy
> trying to figure this thing out.
>
> "B. R.Ramachandran" wrote:
>
> > Hi,
> > It's confusing. I was under the impression that,
> > C4 contains the amount due if paid before due date,
> > D4 contains the due date,
> > J4 contains the late fee (if payment is made more than 10 days after
> > D4), and
> > K4 contains the balance due (which would be D4 if paid within 10 days
> > after D4 or D4+J4 if paid later than 10 days after.
> > But from your recent response it looks like
> > C4 contains the amount paid (not the amount to be paid as I thought),
> > D4 contains the date when payment was made (not the due date as I
> > thought)
> > E4 contains the amount due if paid before on or before due date, and
> > J4, and K4 as above.
> > Is the due date always the 1st of the month and the curstomer has a grace
> > period upto the 10th of the month?
> > Also, if the payment (C4) is made later than 10th I understand that a $25
> > late fee is imposed; but why do you want K4 to be C4+E4+J4 (don't you have to
> > SUBTRACT J4 from E4+J4 (i.e., E4+J4-J4), or am I missing something?
> >
> > Regards
> > B.R.Ramachandran
> >
> > "Nadji" wrote:
> >
> > > Thanks so much half of the formula is working. When I put ithe formula in J4
> > > it leaves the box blank even though I changed the date. The actual late fee
> > > is $25 not 5% I just took a random number($200) I need a formula for J4 to
> > > pick up on the date and automatically add $25.
> > > Example:
> > > C=amt pd
> > > D=date pd
> > > E=monthly amt
> > > J=late fee
> > > K= bal due
> > >
> > > now I need for J to add $25 late fee if D is later than the 10th and K to
> > > also recognize if Dis after the 10th then add C,E,J but if not then K will
> > > equal zero. Is that possible or am I asking too much. ;-} Thanks so much.
> > >
> > > "Nadji" wrote:
> > >
> > > > when i put in IFTODAY()-D4>10,SUM(C4,J4),0 it comes up false I also tried
> > > > IFTODAY()-D4>10,C4*5%,0) same thing. Here is an example of what I'm trying
> > > > to get:
> > > >
> > > > C=Amt pd
> > > > D=date pd
> > > > J=late fee
> > > > K=bal due
> > > >
> > > > Now I'm trying to get k to find the result of C+J if D is after the 6/10/05
> > > > so if i put $200 in C on the 11th then J should add $25 and K should equal
> > > > $225.
.

Quantcast