+ Reply to Thread
Results 1 to 5 of 5

How to calculate past rent due in months

  1. #1
    Registered User
    Join Date
    01-19-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    7

    How to calculate past rent due in months

    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!
    Attached Files Attached Files
    Last edited by noviceone; 01-20-2012 at 09:52 PM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: How to calculate past rent due in months

    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?

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: How to calculate past rent due in months

    Would this work?

    =IF(F2>E2,ROUNDUP((F2-E2)/30,0),0)
    Last edited by JieJenn; 01-20-2012 at 07:15 PM. Reason: an error with the formula

  4. #4
    Registered User
    Join Date
    01-19-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: How to calculate past rent due in months

    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...

  5. #5
    Registered User
    Join Date
    01-25-2012
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: How to calculate past rent due in months

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1