worksheet function

[Nadji]

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.

 

[gls858]

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.

Unless you typed it incorrectly it should be

=IF(TODAY()-D4>10,SUM(C4,J4),0)
Notice the equal sign and the extra set of parenthesis.

gls858

 

[Nadji]

Thanks but now it comes out to be zero I

Unless you typed it incorrectly it should be

=IF(TODAY()-D4>10,SUM(C4,J4),0)
Notice the equal sign and the extra set of parenthesis.

gls858

 

[B. R.Ramachandran]

Hi,
It should be =IF(TODAY()-D4>10,SUM(C4,J4),C4), and this formula goes in K4.
Better would be, a formula in J4 as =If(TODAY()-D4>10,5%*C4,0)
and a formula in K4 as =C4+J4. Here, the late fee would be reading 0 till
10 days after the due date.
By the way, $25 (late fee) is NOT 5% of the payment due ($200) in your
example (but in the second formula in your posting you have shown 5%)
B.R.Ramachandran

 

[Nadji]

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.

 

[B. R.Ramachandran]

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]

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]

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]

Thank you so much, I'm sorry I didn't get back o you but i just checked my
email today, I had to leave that worksheet alone for a few days and get back
to some other work that i was letting fall behind because i became obsessed
with this.:-} I will try the formula and let you know what happens. The
late will not be waived even if it is paid on the 10th at midnight which
actually becomes the 11th. i guess I will have to figure out what to do for
dec into jan when that time come once again thank you.