Adding late fees automatically

N

Nick Xylas

I work for a property management company and details of our rental
properties are stored in Excel workbooks, one month to a worksheet. I
have been asked to make the spreadsheets automatically change the value
of the "Late fee" column to $60 on the 5th of each month if the rent
has not been paid (ie if the following column is blank). I need to
know if this is possible in Excel, and if so, how do I do it?
 
B

Bob Phillips

=IF(AND(B2="",DAY(TODAY())=>5),60,0)

assuming the rent is in B2

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
N

Nick Xylas

Bob said:
=IF(AND(B2="",DAY(TODAY())=>5),60,0)

assuming the rent is in B2
Click to expand...

I tried this (with the column name suitably adjusted), but got an error
message. Also, do I need to change that 5 in the TODAY function to the
serial number of, say, 5th September?
 
B

Bob Phillips

Nick Xylas said:
I tried this (with the column name suitably adjusted), but got an error
message.
Click to expand...

Sorry, my mistake, got the GT sign wrong. Try

=IF(AND(B2="",DAY(TODAY())>=5),60,0)
Also, do I need to change that 5 in the TODAY function to the
serial number of, say, 5th September?
Click to expand...

No, as that will work for any month.
 
C

ccfatzin

Nick-

You may want to adjust the formula below to take into account if the
tenant has only paid some of the rent. I imagine you will want to have
a late fee if the person is not 100% paid up right? If that were the
case adjust the below formula to read one of the following ways:

=IF(AND(B2<A2,DAY(TODAY())=>5),60,0)

This assumes you have the total rent due in column A.

Or....in a unique situation where everyone pays the same rent you can
do the following:

=IF(AND(B2<800,DAY(TODAY())=>5),60,0)

This assumes everyones rent is 800 per month.

-Chris
 
A

Ark John

Nick
How does Excel work for you?
I am a property manager and am looking for something I can use. I
charge utilities....including gas charges that I have a spreadsheet I
need to import.

Thanks
John
 
Top