Need help refining this formula

H

H Halliburton

Right now I have this formula

=IF(OR(B61="",DAY(B61)>=19),48.44,0)

The problem I'm having is if the month or year changes it isn't reconizing
that the payment is late. How do I change it so it will calculate that the
payment is late.

Thanks for any help!
 
H

H Halliburton

Right now I have this formula

=IF(OR(B61="",DAY(B61)>=19),48.44,0)

The problem I'm having is if the month or year changes it isn't reconizing
that the payment is late. How do I change it so it will calculate that the
payment is late.

I guess I sould of said that column A has the payment due date and Column B
has the date the payment was made.

Thanks

Thanks for any help!
 
M

muddan madhu

try this

Cell A1 - payment date
Cell B1 - Due Date
Cell C1 =IF(OR(A1="",(IF(B1>A1,(B1-A1),-(B1-A1))>=19)),48.44,0)
 
T

TomPl

Your formula returns 48.44 if the date in column B is the 19th or greater of
any month.
This formula returns 48.44 if the date in column B is 19 or more days past
the due date:

=IF(OR(B61="",B61-A61>=19),48.44,0)
 
R

Rick Rothstein

Where did the 19 come from? Is that a day to check against no matter what
day is shown in the payment due date? Or is that the number in the payment
due date and you just manually entered it in your formula? My guess is that
you would want to know whenever Column B's date is later than Column A's
date, right? If so...

=IF(OR(B61="",B61>A61),48.44,0)

although perhaps you may want the > to be >= instead.
 
D

David Biddulph

=IF(OR(B61="",B61-A61>=19),48.44,0) if you are allowing 19 days of grace.
Your original formula wasn't taking any notice of column A, so it isn't
clear what you were trying to do. If the 19 in your formula was because
your due date was the 19th of the month, then your formula could be
=IF(OR(B61="",B61>=A61),48.44,0)
Change >= to > if necessary.
 
H

H Halliburton

Rick,

Column A has the date that the payment is due and Column B is the date the
payment was made. There is a 5 day grace period before the payment would be
considered late, in this case the payment is due on the 14th but not
considered late until the 19th. Does that make any sense?
 
R

Rick Rothstein

You conditions are still unclear. Is the due date **always** the 14th of the
month? If so, try this...

=IF(OR(B61="",B61>=DATE(YEAR(A61),MONTH(A61),19)),48.44,0)

And if not (that is, 5 grace days added to the due date in Column A), then
try this...

=IF(OR(B61="",B61>=A61+5),48.44,0)

--
Rick (MVP - Excel)


H Halliburton said:
Rick,

Column A has the date that the payment is due and Column B is the date the
payment was made. There is a 5 day grace period before the payment would
be
considered late, in this case the payment is due on the 14th but not
considered late until the 19th. Does that make any sense?
Click to expand...
 
Top