I am trying to figure out how to calculate how much money tenants owe after being X number of months late in rent. I have attached an example spreadsheet called "Monthly Rent Chart Samples"
Apt. #1 - Mary has paid her rent through 12/31/11 and is 1 month late. I did put in the formula for today's date. I then tried using the formula =DATEDIF(E2,F2,"m") however that does not work in two out of the three apartments. Apt. #1 is to be considered 1 month late since she has not paid her rent for January, even though there is not one month's time passed between 12/31/11 and today (1/20/12).
Apt. #2 - Jane is paid through 1/31/12 and is not late.
Apt. #3 - Karen has paid through 11/30/11, so she owes 2 months rent for December and January ($750 x 2 months = $1500)
Any help would be much appreciated - thank you so much!
Last edited by noviceone; 01-20-2012 at 08:52 PM.
In cell G2 the formula:
=IF(E2>=F2,0,MONTH(F2-E2))
Might give you what you're looking for, but without a bit more information it's hard to be sure.
Mary has paid up to 31st December 2011, does that mean on January 1st 2012 she becomes a month overdue, or does she not become a month overdue until 31st January?
Would this work?
=IF(F2>E2,ROUNDUP((F2-E2)/30,0),0)
Last edited by JieJenn; 01-20-2012 at 06:15 PM. Reason: an error with the formula
To thank someone who has helped you, click on the star icon below their name.
I hate reading
Portfolio
I need a job.I am young and incompetent
THANK YOU so much!! I really appreciate it. Yes that works great!
Now I just need to figure out how to label this as solved...
Hi,
My Q is when Payment Due if due by the end of the month i think this formula is can work
=IFERROR(DATEDIF(E2,F2,"ym"),0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks